SQL Constraints

SQL Constraints are rules enforced on data columns to maintain data integrity, accuracy, and consistency. They prevent invalid data from being entered into the database.

Constraint Types Overview

graph TB
    A[SQL Constraints] --> B[PRIMARY KEY]
    A --> C[FOREIGN KEY]
    A --> D[UNIQUE]
    A --> E[NOT NULL]
    A --> F[CHECK]
    A --> G[DEFAULT]
    
    B --> B1[Unique identifier]
    B --> B2[Cannot be NULL]
    
    C --> C1[Referential integrity]
    C --> C2[Links tables]
    
    D --> D1[No duplicates]
    D --> D2[Allows one NULL]
    
    E --> E1[Required value]
    E --> E2[No NULL allowed]
    
    F --> F1[Validates condition]
    F --> F2[Business rules]
    
    G --> G1[Default value]
    G --> G2[When not specified]
    
    style A fill:#2196F3
    style B fill:#4CAF50
    style C fill:#FF9800
    style D fill:#9C27B0
    style E fill:#F44336
    style F fill:#00BCD4
    style G fill:#E91E63

Key Points:

• PRIMARY KEY: Uniquely identifies each row, auto-indexed
• FOREIGN KEY: Maintains relationships between tables
• UNIQUE: Ensures no duplicate values in column
• NOT NULL: Requires value, prevents NULL
• CHECK: Validates data against custom conditions
• DEFAULT: Provides fallback value when none specified

PRIMARY KEY Behavior

sequenceDiagram
    participant App as Application
    participant DB as Database
    participant PK as Primary Key
    participant Index as Index
    
    App->>DB: INSERT new row
    DB->>PK: Check uniqueness
    PK->>PK: Validate not NULL
    
    alt Duplicate or NULL
        PK->>App: Error: Constraint violation
    else Valid
        PK->>Index: Add to index
        Index->>DB: Store row
        DB->>App: Success
    end

Key Points:

• Uniqueness: No two rows can have same primary key value
• NOT NULL: Primary key cannot be NULL
• Auto-Index: Automatically creates clustered index
• Single per Table: Only one primary key per table
• Composite: Can span multiple columns

FOREIGN KEY Relationships

graph LR
    A[Parent Table: customers] -->|customer_id PK| B[Relationship]
    B -->|customer_id FK| C[Child Table: orders]
    
    D[Action on Parent] --> E{CASCADE Option}
    E -->|DELETE CASCADE| F[Delete child rows]
    E -->|SET NULL| G[Set FK to NULL]
    E -->|RESTRICT| H[Prevent deletion]
    
    style A fill:#4CAF50
    style C fill:#FF9800
    style F fill:#F44336
    style G fill:#9C27B0
    style H fill:#2196F3

Key Points:

• Referential Integrity: Child FK must match parent PK
• CASCADE: Automatically propagate changes to children
• SET NULL: Set FK to NULL when parent deleted
• RESTRICT: Prevent parent deletion if children exist
• Performance: Index foreign key columns for better JOIN performance

CASCADE Options Comparison

flowchart TB
    A[Parent Row Deleted] --> B{CASCADE Option?}
    
    B -->|CASCADE| C[Delete all child rows]
    B -->|SET NULL| D[Set FK to NULL in children]
    B -->|RESTRICT| E[Prevent deletion - Error]
    B -->|NO ACTION| F[Same as RESTRICT]
    B -->|SET DEFAULT| G[Set FK to default value]
    
    style C fill:#F44336
    style D fill:#FF9800
    style E fill:#2196F3
    style F fill:#2196F3
    style G fill:#9C27B0

Key Points:

• CASCADE: Use for dependent data (order items)
• SET NULL: Use for optional relationships
• RESTRICT: Use to protect critical data
• NO ACTION: Default behavior, same as RESTRICT
• SET DEFAULT: Rarely used, requires default value

UNIQUE Constraint Behavior

graph TB
    A[INSERT/UPDATE] --> B{Check UNIQUE}
    B -->|Value exists| C[Error: Duplicate]
    B -->|Value is NULL| D{NULL allowed?}
    B -->|Value unique| E[Allow operation]
    
    D -->|First NULL| E
    D -->|Multiple NULLs| F{Database Type}
    
    F -->|MySQL/PostgreSQL| E
    F -->|SQL Server| C
    
    style C fill:#F44336
    style E fill:#4CAF50

Key Points:

• No Duplicates: Ensures all non-NULL values are unique
• NULL Handling: Most databases allow multiple NULLs
• Auto-Index: Creates non-clustered index automatically
• Composite: Can span multiple columns
• vs PRIMARY KEY: UNIQUE allows NULL, multiple per table

CHECK Constraint Validation

sequenceDiagram
    participant App as Application
    participant DB as Database
    participant Check as CHECK Constraint
    
    App->>DB: INSERT/UPDATE data
    DB->>Check: Evaluate condition
    
    alt Condition FALSE
        Check->>App: Error: Check violation
    else Condition TRUE
        Check->>DB: Allow operation
        DB->>App: Success
    end
    
    Note over Check: Examples: price > 0, age >= 18

Key Points:

• Business Rules: Enforce domain-specific validation
• Boolean Expression: Must evaluate to TRUE or FALSE
• Multiple Conditions: Can combine with AND/OR
• Column References: Can reference other columns in same row
• Performance: Evaluated on every INSERT/UPDATE

Constraint Enforcement Order

flowchart TB
    A[Data Modification] --> B[NOT NULL Check]
    B --> C[CHECK Constraint]
    C --> D[UNIQUE Constraint]
    D --> E[PRIMARY KEY Check]
    E --> F[FOREIGN KEY Check]
    F --> G{All Pass?}
    
    G -->|Yes| H[Commit Changes]
    G -->|No| I[Rollback - Error]
    
    style H fill:#4CAF50
    style I fill:#F44336

Key Points:

• Order Matters: Constraints checked in specific sequence
• NOT NULL First: Simplest validation
• CHECK Next: Custom business rules
• UNIQUE/PRIMARY: Uniqueness validation
• FOREIGN KEY Last: Referential integrity

DEFAULT Constraint Flow

flowchart LR
    A[INSERT Statement] --> B{Column Value Provided?}
    B -->|Yes| C[Use provided value]
    B -->|No| D{DEFAULT defined?}
    
    D -->|Yes| E[Use DEFAULT value]
    D -->|No| F{NOT NULL?}
    
    F -->|Yes| G[Error: NULL not allowed]
    F -->|No| H[Insert NULL]
    
    C --> I[Insert row]
    E --> I
    H --> I
    
    style G fill:#F44336
    style I fill:#4CAF50

Key Points:

• Automatic: Applied when no value specified
• Expressions: Can use functions (NOW, UUID)
• NOT NULL Compatible: Works with NOT NULL constraint
• Update: Not applied on UPDATE, only INSERT
• Literal Values: Can be constants or expressions

Code Examples

PRIMARY KEY

-- Single column primary key
CREATE TABLE customers (
    customer_id INT PRIMARY KEY AUTO_INCREMENT,
    email VARCHAR(100) NOT NULL,
    name VARCHAR(100) NOT NULL
);

-- Composite primary key
CREATE TABLE order_items (
    order_id INT,
    product_id INT,
    quantity INT NOT NULL,
    PRIMARY KEY (order_id, product_id)
);

-- Named constraint
CREATE TABLE users (
    user_id INT,
    username VARCHAR(50),
    CONSTRAINT pk_users PRIMARY KEY (user_id)
);

FOREIGN KEY

-- Basic foreign key
CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    customer_id INT,
    order_date DATE NOT NULL,
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);

-- With CASCADE options
CREATE TABLE order_items (
    item_id INT PRIMARY KEY,
    order_id INT,
    product_id INT,
    CONSTRAINT fk_order FOREIGN KEY (order_id) 
        REFERENCES orders(order_id) ON DELETE CASCADE,
    CONSTRAINT fk_product FOREIGN KEY (product_id) 
        REFERENCES products(product_id) ON DELETE RESTRICT
);

UNIQUE, NOT NULL, CHECK

CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    email VARCHAR(100) UNIQUE NOT NULL,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    salary DECIMAL(10,2) NOT NULL,
    age INT NOT NULL,
    hire_date DATE NOT NULL,
    
    -- CHECK constraints
    CONSTRAINT chk_salary CHECK (salary > 0 AND salary <= 1000000),
    CONSTRAINT chk_age CHECK (age >= 18 AND age <= 65),
    CONSTRAINT chk_email CHECK (email LIKE '%@%.%')
);

DEFAULT Constraint

CREATE TABLE products (
    product_id INT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    price DECIMAL(10,2) NOT NULL DEFAULT 0.00,
    stock INT NOT NULL DEFAULT 0,
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

Managing Constraints

-- Add constraints to existing table
ALTER TABLE customers
ADD CONSTRAINT uq_email UNIQUE (email);

ALTER TABLE orders
ADD CONSTRAINT fk_customer FOREIGN KEY (customer_id) 
    REFERENCES customers(customer_id) ON DELETE CASCADE;

ALTER TABLE products
ADD CONSTRAINT chk_price CHECK (price > 0);

-- Drop constraints
ALTER TABLE customers DROP CONSTRAINT uq_email;
ALTER TABLE orders DROP FOREIGN KEY fk_customer;
ALTER TABLE products DROP CONSTRAINT chk_price;

-- Modify NOT NULL
ALTER TABLE employees MODIFY COLUMN phone VARCHAR(20) NOT NULL;
ALTER TABLE employees MODIFY COLUMN phone VARCHAR(20) NULL;

Composite Constraints

-- Composite UNIQUE
CREATE TABLE course_schedule (
    schedule_id INT PRIMARY KEY,
    course_id INT,
    day_of_week VARCHAR(10),
    time_slot TIME,
    room_number VARCHAR(10),
    UNIQUE (course_id, day_of_week, time_slot),
    UNIQUE (day_of_week, time_slot, room_number)
);

Best Practices

1. Always Use PRIMARY KEY: Every table should have a primary key
2. Name Constraints: Use descriptive names (pk_, fk_, uq_, chk_)
3. Index Foreign Keys: Improves JOIN performance
4. Choose CASCADE Wisely: CASCADE for dependent, RESTRICT for independent
5. Validate at Database: Use CHECK for business rules
6. NOT NULL for Required: Mark required fields as NOT NULL
7. Sensible Defaults: Provide DEFAULT values when appropriate
8. Document Constraints: Comment complex CHECK conditions

---