Constraints

Constraints are rules applied to columns or tables to preserve data integrity.

Types of Constraints

1. PRIMARY KEY

Marks a column as the primary unique identifier for each row. Each table can have only one PRIMARY KEY.

CREATE TABLE users (
    id INT PRIMARY KEY AUTO_RANDOM,
    username VARCHAR(50) NOT NULL
);

2. FOREIGN KEY

Enforces referential integrity between tables. The value in the column must exist in the referenced table.

CREATE TABLE orders (
    id INT PRIMARY KEY AUTO_RANDOM,
    user_id INT NOT NULL,
    total DECIMAL(12, 2),
    FOREIGN KEY (user_id) REFERENCES users(id)
        ON DELETE CASCADE
        ON UPDATE CASCADE
);

ON DELETE and ON UPDATE options:

• CASCADE — automatically delete/update related rows
• SET NULL — set to NULL if the referenced row is deleted
• RESTRICT — reject the operation if references exist

3. UNIQUE

Ensures all values in the column are unique.

CREATE TABLE users (
    id INT PRIMARY KEY AUTO_RANDOM,
    email VARCHAR(100) NOT NULL UNIQUE,
    username VARCHAR(50) NOT NULL UNIQUE
);

4. NOT NULL

Prevents NULL values in the column.

CREATE TABLE products (
    id INT PRIMARY KEY AUTO_RANDOM,
    name VARCHAR(200) NOT NULL,
    price DECIMAL(10, 2) NOT NULL
);

5. DEFAULT

Sets a default value if no value is provided.

CREATE TABLE orders (
    id INT PRIMARY KEY AUTO_RANDOM,
    status VARCHAR(20) DEFAULT 'pending',
    quantity INT DEFAULT 1
);

6. CHECK

Validates values against a specified condition.

CREATE TABLE accounts (
    id INT PRIMARY KEY AUTO_RANDOM,
    balance DECIMAL(12, 2) CHECK (balance >= 0),
    email VARCHAR(100) CHECK (email LIKE '%@%')
);

Composite Primary Key

CREATE TABLE order_items (
    order_id INT,
    product_id INT,
    quantity INT NOT NULL,
    price DECIMAL(10, 2) NOT NULL,
    PRIMARY KEY (order_id, product_id)
);

Composite UNIQUE

CREATE TABLE user_roles (
    user_id INT,
    role_id INT,
    assigned_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY unique_user_role (user_id, role_id)
);

Adding/Dropping Constraints

-- Add a UNIQUE constraint
ALTER TABLE users ADD UNIQUE KEY uk_email (email);

-- Add a CHECK constraint
ALTER TABLE products ADD CONSTRAINT chk_price CHECK (price > 0);

-- Drop the CHECK constraint
ALTER TABLE products DROP CONSTRAINT chk_price;

-- Show all constraints
SHOW CREATE TABLE users;

In the next chapter, we will look at INSERT, UPDATE, and DELETE in TiDB.