task12.sql

--SQL Schema Demonstrating Constraint Usage
--Example Use Case: User & Orders System
--Create a database
CREATE DATABASE task12;
USE task12;
--Table 1: users
CREATE TABLE users (
    user_id INT AUTO_INCREMENT PRIMARY KEY,
    
    email VARCHAR(255) UNIQUE NOT NULL,
    
    age INT CHECK (age BETWEEN 18 AND 60),
    
    gender CHAR(1) CHECK (gender IN ('M', 'F', 'O')),
    
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);


--Demonstrates:

--PRIMARY KEY

--UNIQUE

CHECK (range + enum-style)

DEFAULT timestamp

--Table 2: orders
CREATE TABLE orders (
    order_id INT AUTO_INCREMENT PRIMARY KEY,
    
    user_id INT NOT NULL,
    
    order_amount DECIMAL(10,2)
        CHECK (order_amount > 0),
    
    order_status VARCHAR(20)
        DEFAULT 'PENDING'
        CHECK (order_status IN ('PENDING', 'PAID', 'CANCELLED')),
    
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    UNIQUE (user_id, created_at),
    
    FOREIGN KEY (user_id) REFERENCES users(user_id)
);


--Demonstrates:

--Multiple constraints on one column

--Composite UNIQUE

DEFAULT + CHECK together

FOREIGN KEY

--Testing Constraint Violations (Intentional)
--CHECK violation
INSERT INTO users (email, age, gender)
VALUES ('test@example.com', 15, 'M');
-- Fails: age must be between 18 and 60

--UNIQUE violation
INSERT INTO users (email, age, gender)
VALUES ('test@example.com', 25, 'F');
-- Fails: email must be unique

--CHECK + DEFAULT validation
INSERT INTO orders (user_id, order_amount, order_status)
VALUES (1, -100, 'PAID');
-- Fails: order_amount must be > 0

--Constraint Enforcement Order (Important Concept)

--Typical enforcement order:

--NOT NULL

CHECK

UNIQUE

FOREIGN KEY

--Example:
If order_amount is NULL and negative, NOT NULL fails before CHECK.

--Database vs Application-Level Validation
Aspect	Database-Level	Application-Level
Enforcement	Always	Can be bypassed
Performance	Faster	Slower
Security	Strong	Weaker
Centralized	Yes	No
Best Practice	✅ Mandatory	✅ Complementary

--Rule:

--Application validates user experience, database validates truth.

--Can Constraints Be Removed Later?

--Yes

ALTER TABLE users DROP CHECK age;
ALTER TABLE users DROP INDEX email;
ALTER TABLE orders DROP FOREIGN KEY orders_ibfk_1;

--Use carefully in production.