What are Database Constraints?

Constraints are rules enforced on data columns to ensure accuracy and reliability. They're like security guards for your database, preventing bad data from entering! 🛡️

NOT NULL Constraint

-- Creating a table with NOT NULL constraints
CREATE TABLE users (
    user_id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL,
    email VARCHAR(100) NOT NULL,
    phone VARCHAR(20),              -- This can be NULL
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

-- This will succeed
INSERT INTO users (username, email) 
VALUES ('johndoe', 'john@example.com');

-- This will fail - username cannot be NULL
INSERT INTO users (email) 
VALUES ('jane@example.com');
-- ERROR 1048: Column 'username' cannot be null

-- Adding NOT NULL to existing column
ALTER TABLE users 
MODIFY COLUMN phone VARCHAR(20) NOT NULL;

-- Removing NOT NULL constraint
ALTER TABLE users 
MODIFY COLUMN phone VARCHAR(20);

UNIQUE Constraint

-- Single column UNIQUE constraint
CREATE TABLE users (
    user_id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) UNIQUE,
    email VARCHAR(100),
    UNIQUE KEY unique_email (email)  -- Named constraint
);

-- Composite UNIQUE constraint (combination must be unique)
CREATE TABLE enrollments (
    enrollment_id INT AUTO_INCREMENT PRIMARY KEY,
    student_id INT NOT NULL,
    course_id INT NOT NULL,
    enrollment_date DATE,
    UNIQUE KEY unique_enrollment (student_id, course_id)
);

-- Adding UNIQUE constraint to existing table
ALTER TABLE products 
ADD UNIQUE (product_code);

-- Adding named UNIQUE constraint
ALTER TABLE customers 
ADD CONSTRAINT unique_customer_email UNIQUE (email);

-- Dropping UNIQUE constraint
ALTER TABLE users 
DROP INDEX username;  -- Uses the column name

-- Dropping named constraint
ALTER TABLE customers 
DROP INDEX unique_customer_email;

-- Handling duplicate key errors in application
-- Error: Duplicate entry 'john@example.com' for key 'email'

DEFAULT Constraint

-- DEFAULT constraint examples
CREATE TABLE tasks (
    task_id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(200) NOT NULL,
    status ENUM('pending', 'in_progress', 'completed') DEFAULT 'pending',
    priority INT DEFAULT 5,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    is_active BOOLEAN DEFAULT TRUE,
    assigned_to VARCHAR(50) DEFAULT 'unassigned'
);

-- Insert using defaults
INSERT INTO tasks (title) 
VALUES ('Complete project');
-- status='pending', priority=5, is_active=TRUE, assigned_to='unassigned'

-- Override default values
INSERT INTO tasks (title, status, priority) 
VALUES ('Urgent task', 'in_progress', 10);

-- DEFAULT with expressions (MySQL 8.0+)
CREATE TABLE orders (
    order_id INT AUTO_INCREMENT PRIMARY KEY,
    order_number VARCHAR(20) DEFAULT (CONCAT('ORD-', LPAD(order_id, 6, '0'))),
    order_date DATE DEFAULT (CURRENT_DATE),
    due_date DATE DEFAULT (DATE_ADD(CURRENT_DATE, INTERVAL 7 DAY))
);

-- Adding DEFAULT to existing column
ALTER TABLE users 
ALTER COLUMN status SET DEFAULT 'active';

-- Removing DEFAULT constraint
ALTER TABLE users 
ALTER COLUMN status DROP DEFAULT;

CHECK Constraint

-- Basic CHECK constraints
CREATE TABLE products (
    product_id INT AUTO_INCREMENT PRIMARY KEY,
    product_name VARCHAR(100) NOT NULL,
    price DECIMAL(10,2) CHECK (price > 0),
    quantity INT CHECK (quantity >= 0),
    discount_percent INT CHECK (discount_percent BETWEEN 0 AND 100)
);

-- Complex CHECK constraints
CREATE TABLE employees (
    emp_id INT AUTO_INCREMENT PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    email VARCHAR(100) UNIQUE,
    salary DECIMAL(10,2),
    hire_date DATE,
    birth_date DATE,
    CONSTRAINT chk_salary CHECK (salary >= 30000),
    CONSTRAINT chk_email CHECK (email LIKE '%@%.%'),
    CONSTRAINT chk_dates CHECK (hire_date > birth_date),
    CONSTRAINT chk_age CHECK (DATEDIFF(CURDATE(), birth_date) >= 6570) -- 18 years
);

-- Table-level CHECK with multiple columns
CREATE TABLE orders (
    order_id INT AUTO_INCREMENT PRIMARY KEY,
    order_date DATE NOT NULL,
    ship_date DATE,
    total_amount DECIMAL(10,2),
    discount_amount DECIMAL(10,2) DEFAULT 0,
    CONSTRAINT chk_ship_date CHECK (ship_date >= order_date),
    CONSTRAINT chk_discount CHECK (discount_amount <= total_amount)
);

-- Adding CHECK constraint to existing table
ALTER TABLE products 
ADD CONSTRAINT chk_price_range CHECK (price BETWEEN 0.01 AND 999999.99);

-- Dropping CHECK constraint
ALTER TABLE products 
DROP CONSTRAINT chk_price_range;

-- CHECK with CASE expression
CREATE TABLE subscriptions (
    sub_id INT AUTO_INCREMENT PRIMARY KEY,
    plan_type ENUM('basic', 'premium', 'enterprise'),
    user_limit INT,
    CONSTRAINT chk_user_limit CHECK (
        CASE 
            WHEN plan_type = 'basic' THEN user_limit <= 5
            WHEN plan_type = 'premium' THEN user_limit <= 50
            WHEN plan_type = 'enterprise' THEN user_limit <= 999999
            ELSE FALSE
        END
    )
);

Working with Multiple Constraints

-- Real-world example: E-commerce database
CREATE TABLE products (
    product_id INT AUTO_INCREMENT PRIMARY KEY,
    sku VARCHAR(50) NOT NULL UNIQUE,
    name VARCHAR(200) NOT NULL,
    description TEXT,
    category_id INT NOT NULL,
    price DECIMAL(10,2) NOT NULL CHECK (price > 0),
    cost DECIMAL(10,2) CHECK (cost >= 0),
    stock_quantity INT NOT NULL DEFAULT 0 CHECK (stock_quantity >= 0),
    min_stock_level INT DEFAULT 10 CHECK (min_stock_level >= 0),
    is_active BOOLEAN NOT NULL DEFAULT TRUE,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    -- Foreign key constraint
    FOREIGN KEY (category_id) REFERENCES categories(category_id),
    
    -- Table-level constraints
    CONSTRAINT chk_price_greater_than_cost CHECK (price > cost),
    CONSTRAINT chk_min_stock CHECK (min_stock_level <= stock_quantity OR stock_quantity = 0),
    
    -- Indexes for performance
    INDEX idx_category (category_id),
    INDEX idx_active_products (is_active, stock_quantity)
);

-- User registration table with comprehensive constraints
CREATE TABLE user_accounts (
    user_id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(30) NOT NULL UNIQUE 
        CHECK (LENGTH(username) >= 3 AND username REGEXP '^[a-zA-Z0-9_]+$'),
    email VARCHAR(255) NOT NULL UNIQUE 
        CHECK (email REGEXP '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\\.[A-Za-z]{2,}$'),
    password_hash CHAR(60) NOT NULL, -- For bcrypt hash
    first_name VARCHAR(50) NOT NULL CHECK (LENGTH(first_name) >= 2),
    last_name VARCHAR(50) NOT NULL CHECK (LENGTH(last_name) >= 2),
    phone VARCHAR(20) UNIQUE CHECK (phone REGEXP '^[0-9+\\-() ]+$'),
    birth_date DATE NOT NULL CHECK (birth_date <= DATE_SUB(CURDATE(), INTERVAL 13 YEAR)),
    account_status ENUM('pending', 'active', 'suspended', 'deleted') 
        NOT NULL DEFAULT 'pending',
    email_verified BOOLEAN NOT NULL DEFAULT FALSE,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    last_login TIMESTAMP NULL,
    
    -- Ensure last_login is after creation
    CONSTRAINT chk_login_after_creation CHECK (last_login IS NULL OR last_login >= created_at)
);

-- Order table with business logic constraints
CREATE TABLE orders (
    order_id INT AUTO_INCREMENT PRIMARY KEY,
    order_number VARCHAR(20) NOT NULL UNIQUE,
    customer_id INT NOT NULL,
    order_date TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    ship_date DATE,
    delivery_date DATE,
    subtotal DECIMAL(10,2) NOT NULL CHECK (subtotal >= 0),
    tax_amount DECIMAL(10,2) NOT NULL DEFAULT 0 CHECK (tax_amount >= 0),
    shipping_cost DECIMAL(10,2) NOT NULL DEFAULT 0 CHECK (shipping_cost >= 0),
    discount_amount DECIMAL(10,2) DEFAULT 0 CHECK (discount_amount >= 0),
    total_amount DECIMAL(10,2) NOT NULL,
    status ENUM('pending', 'processing', 'shipped', 'delivered', 'cancelled') 
        NOT NULL DEFAULT 'pending',
    
    FOREIGN KEY (customer_id) REFERENCES user_accounts(user_id),
    
    -- Business logic constraints
    CONSTRAINT chk_dates_order CHECK (
        (ship_date IS NULL OR ship_date >= DATE(order_date)) AND
        (delivery_date IS NULL OR delivery_date >= ship_date)
    ),
    CONSTRAINT chk_amounts CHECK (
        total_amount = subtotal + tax_amount + shipping_cost - discount_amount AND
        discount_amount <= subtotal
    ),
    CONSTRAINT chk_status_dates CHECK (
        (status != 'shipped' OR ship_date IS NOT NULL) AND
        (status != 'delivered' OR delivery_date IS NOT NULL)
    )
);

Handling Constraint Violations

-- Handling constraints in application code (PHP example)
/*
try {
    $pdo->exec("INSERT INTO users (email) VALUES ('invalid')");
} catch (PDOException $e) {
    if ($e->getCode() == 23000) { // Integrity constraint violation
        if (strpos($e->getMessage(), 'Duplicate entry') !== false) {
            echo "Email already exists";
        } elseif (strpos($e->getMessage(), 'cannot be null') !== false) {
            echo "Required field missing";
        } elseif (strpos($e->getMessage(), 'Check constraint') !== false) {
            echo "Invalid data format";
        }
    }
}
*/

-- Checking constraints information
SELECT 
    CONSTRAINT_NAME,
    CONSTRAINT_TYPE,
    TABLE_NAME
FROM information_schema.TABLE_CONSTRAINTS
WHERE TABLE_SCHEMA = 'your_database';

-- View CHECK constraints
SELECT 
    CONSTRAINT_NAME,
    CHECK_CLAUSE
FROM information_schema.CHECK_CONSTRAINTS
WHERE CONSTRAINT_SCHEMA = 'your_database';

-- Temporarily disable foreign key checks (use with caution!)
SET FOREIGN_KEY_CHECKS = 0;
-- Perform operations
SET FOREIGN_KEY_CHECKS = 1;

-- Validate data before inserting (preventive approach)
SELECT COUNT(*) AS violations
FROM products
WHERE price <= cost;  -- Check business rule

Practice Exercise

CREATE DATABASE user_system;
USE user_system;

CREATE TABLE users (
    user_id INT AUTO_INCREMENT PRIMARY KEY,
    
    -- Authentication fields
    username VARCHAR(20) NOT NULL UNIQUE,
    email VARCHAR(255) NOT NULL UNIQUE,
    password_hash CHAR(60) NOT NULL,
    
    -- Profile fields
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    birth_date DATE NOT NULL,
    
    -- Account status
    account_status ENUM('active', 'suspended', 'deleted') 
        NOT NULL DEFAULT 'active',
    email_verified BOOLEAN NOT NULL DEFAULT FALSE,
    
    -- Timestamps
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    last_login_at TIMESTAMP NULL,
    deleted_at TIMESTAMP NULL,
    
    -- Constraints
    CONSTRAINT chk_username_length 
        CHECK (LENGTH(username) BETWEEN 3 AND 20),
    CONSTRAINT chk_username_format 
        CHECK (username REGEXP '^[a-zA-Z0-9_]+$'),
    CONSTRAINT chk_email_format 
        CHECK (email REGEXP '^[^@]+@[^@]+\\.[^@]+$'),
    CONSTRAINT chk_age 
        CHECK (DATEDIFF(CURDATE(), birth_date) >= 4745), -- 13 years
    CONSTRAINT chk_name_length 
        CHECK (LENGTH(first_name) >= 2 AND LENGTH(last_name) >= 2),
    CONSTRAINT chk_deletion 
        CHECK ((account_status != 'deleted') OR (deleted_at IS NOT NULL)),
    
    -- Indexes for performance
    INDEX idx_email_verified (email_verified),
    INDEX idx_account_status (account_status),
    INDEX idx_last_login (last_login_at)
);

-- Session tracking table
CREATE TABLE user_sessions (
    session_id CHAR(36) PRIMARY KEY DEFAULT (UUID()),
    user_id INT NOT NULL,
    ip_address VARCHAR(45) NOT NULL,
    user_agent VARCHAR(255),
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    expires_at TIMESTAMP NOT NULL,
    
    FOREIGN KEY (user_id) REFERENCES users(user_id) 
        ON DELETE CASCADE,
    
    CONSTRAINT chk_session_expiry 
        CHECK (expires_at > created_at),
    
    INDEX idx_user_sessions (user_id),
    INDEX idx_expires (expires_at)
);

-- Password reset tokens
CREATE TABLE password_resets (
    token_id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    token_hash CHAR(64) NOT NULL UNIQUE, -- SHA-256 hash
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    expires_at TIMESTAMP NOT NULL,
    used_at TIMESTAMP NULL,
    
    FOREIGN KEY (user_id) REFERENCES users(user_id) 
        ON DELETE CASCADE,
    
    CONSTRAINT chk_token_expiry 
        CHECK (expires_at > created_at),
    CONSTRAINT chk_token_usage 
        CHECK (used_at IS NULL OR used_at >= created_at),
    
    INDEX idx_token (token_hash),
    INDEX idx_user_tokens (user_id)
);