Constraints

Rules enforced by DB engine on column/row data. Violations rejected at write time. Way better than app-level checks — DB never lies.

The Six Constraints

CREATE TABLE persons (
    id          INT AUTO_INCREMENT PRIMARY KEY,
    email       VARCHAR(255) NOT NULL UNIQUE,
    age         INT CHECK (age >= 18),
    country     CHAR(2) DEFAULT 'IN',
    manager_id  INT,
    FOREIGN KEY (manager_id) REFERENCES persons(id)
        ON DELETE SET NULL
        ON UPDATE CASCADE
);

---

PRIMARY KEY vs UNIQUE

Interview classic.

Composite PK: PRIMARY KEY (order_id, line_item_id) — both together unique.

---

FOREIGN KEY — Referential Integrity

Prevents orphan rows. Critical for relational integrity.

CREATE TABLE orders (
    id INT PRIMARY KEY,
    customer_id INT,
    FOREIGN KEY (customer_id) REFERENCES customers(id)
        ON DELETE CASCADE        -- delete orders when customer deleted
        ON UPDATE CASCADE        -- update FK if PK changes
);

ON DELETE / ON UPDATE actions:

Best practice:

• Use RESTRICT by default — fail loud, force explicit cleanup.
• CASCADE DELETE rarely safe except for tightly-owned children (e.g., order_items under orders).
• Always index FK columns — improves JOINs and prevents lock escalation on parent updates.

---

CHECK Constraints

Inline value rules. Runs on INSERT/UPDATE.

CREATE TABLE products (
    price DECIMAL(10,2) CHECK (price > 0),
    discount_pct INT CHECK (discount_pct BETWEEN 0 AND 100),
    status VARCHAR(20) CHECK (status IN ('active', 'archived', 'draft'))
);

-- Multi-column CHECK
ALTER TABLE events ADD CONSTRAINT chk_dates CHECK (end_time > start_time);

Note: MySQL ignored CHECK pre-8.0.16 — silently parsed but didn't enforce. Verify version if older.

Modern alternative: Use ENUM-like CHECK or generated columns instead of trigger-heavy validation.

---

DEFAULT

CREATE TABLE logs (
    id INT AUTO_INCREMENT PRIMARY KEY,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    level VARCHAR(10) DEFAULT 'INFO'
);

INSERT INTO logs (level) VALUES ('ERROR');  -- created_at auto-filled
INSERT INTO logs () VALUES ();              -- both defaulted

Best practice: DEFAULT CURRENT_TIMESTAMP for created_at is cleaner than app-side. ON UPDATE CURRENT_TIMESTAMP (MySQL) for updated_at.

---

AUTO_INCREMENT (MySQL) / SERIAL (Postgres) / IDENTITY (SQL Server)

Auto-generated unique numbers, usually for surrogate PKs.

-- MySQL
CREATE TABLE users (id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(100));

-- Postgres
CREATE TABLE users (id SERIAL PRIMARY KEY, name VARCHAR(100));
-- modern Postgres: GENERATED ALWAYS AS IDENTITY
CREATE TABLE users (id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY);

-- SQL Server
CREATE TABLE users (id INT IDENTITY(1,1) PRIMARY KEY);

Gotchas:

• Numbers may have gaps (rolled-back INSERT consumes the value).
• TRUNCATE resets the counter; DELETE doesn't.
• Don't expose to clients (security/enumeration); use UUIDs in URLs.

---

Adding/Dropping Constraints

-- Add
ALTER TABLE users ADD CONSTRAINT uk_email UNIQUE (email);
ALTER TABLE orders ADD CONSTRAINT fk_cust FOREIGN KEY (customer_id) REFERENCES customers(id);
ALTER TABLE products ADD CONSTRAINT chk_price CHECK (price > 0);

-- Drop (name varies by DBMS)
ALTER TABLE users DROP CONSTRAINT uk_email;     -- standard
ALTER TABLE users DROP INDEX uk_email;          -- MySQL UNIQUE
ALTER TABLE orders DROP FOREIGN KEY fk_cust;    -- MySQL FK

Best practice: Always name constraints (CONSTRAINT chk_xyz). Auto-generated names are unstable — breaks migrations and error messages.

---

Constraints vs Indexes

Easy to confuse:

• Constraint = rule about valid data.
• Index = data structure for fast lookup.
• PRIMARY KEY and UNIQUE create indexes implicitly.
• FOREIGN KEY does NOT auto-create an index in MySQL InnoDB — must add explicitly for JOIN perf. Postgres also requires explicit index on FK column.
• CHECK and NOT NULL create no index.

See [[Indexes & Performance]].

---

Cross-refs

• [[DDL DML DCL TCL]] — ALTER TABLE syntax
• [[Indexes & Performance]] — why FKs need indexes
• [[Basics]] — NULL semantics