Constraints

Constraints are rules applied to table columns to ensure data integrity. This chapter introduces various types of constraints.

Types of Constraints

1. PRIMARY KEY - Unique identifier
2. FOREIGN KEY - Referential integrity
3. UNIQUE - Unique values
4. NOT NULL - No null values
5. CHECK - Value validation
6. DEFAULT - Default values

PRIMARY KEY

-- Single column primary key
CREATE TABLE users (
    id INT PRIMARY KEY,
    name VARCHAR(50)
);

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

-- Add primary key to existing table
ALTER TABLE users
ADD PRIMARY KEY (id);

FOREIGN KEY

-- Create foreign key
CREATE TABLE orders (
    id INT PRIMARY KEY,
    user_id INT,
    FOREIGN KEY (user_id) REFERENCES users(id)
);

-- Foreign key with cascade
CREATE TABLE orders (
    id INT PRIMARY KEY,
    user_id INT,
    FOREIGN KEY (user_id) REFERENCES users(id)
        ON DELETE CASCADE
        ON UPDATE CASCADE
);

UNIQUE

-- Unique constraint
CREATE TABLE users (
    id INT PRIMARY KEY,
    email VARCHAR(100) UNIQUE,
    username VARCHAR(50) UNIQUE
);

-- Add unique constraint
ALTER TABLE users
ADD UNIQUE (email);

NOT NULL

-- Not null constraint
CREATE TABLE users (
    id INT PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    email VARCHAR(100) NOT NULL
);

CHECK

-- Check constraint
CREATE TABLE users (
    id INT PRIMARY KEY,
    age INT CHECK (age >= 18),
    email VARCHAR(100) CHECK (email LIKE '%@%')
);

DEFAULT

-- Default value
CREATE TABLE users (
    id INT PRIMARY KEY,
    status VARCHAR(20) DEFAULT 'active',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Summary

• PRIMARY KEY: Unique identifier
• FOREIGN KEY: Referential integrity
• UNIQUE: Unique values
• NOT NULL: Required values
• CHECK: Value validation
• DEFAULT: Default values

Next Step: Learn VIEWS