Constraints

-- Column-level
CREATE TABLE users (
    id    INTEGER     NOT NULL,
    email VARCHAR(255) NOT NULL,
    phone VARCHAR(20)            -- nullable (default)
);

-- Add NOT NULL later
ALTER TABLE users ALTER COLUMN phone SET NOT NULL;

-- Remove NOT NULL
ALTER TABLE users ALTER COLUMN phone DROP NOT NULL;

-- Note: NOT NULL cannot be named (no constraint name); it is not a named constraint.

-- Single-column primary key (column-level)
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    email TEXT NOT NULL
);

-- Multi-column primary key (table-level only)
CREATE TABLE order_items (
    order_id   INTEGER NOT NULL,
    product_id INTEGER NOT NULL,
    qty        INTEGER NOT NULL,
    PRIMARY KEY (order_id, product_id)
);

-- Named primary key
CREATE TABLE users (
    id SERIAL,
    CONSTRAINT pk_users PRIMARY KEY (id)
);

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

-- Drop primary key
ALTER TABLE products DROP CONSTRAINT pk_products;
ALTER TABLE products DROP PRIMARY KEY;         -- MySQL shorthand

-- Single column unique (column-level)
CREATE TABLE users (
    email VARCHAR(255) UNIQUE
);

-- Named unique constraint (column or table level)
CREATE TABLE users (
    email VARCHAR(255),
    CONSTRAINT uq_users_email UNIQUE (email)
);

-- Multi-column unique (table-level — combination must be unique)
CREATE TABLE team_members (
    team_id INTEGER NOT NULL,
    user_id INTEGER NOT NULL,
    CONSTRAINT uq_team_user UNIQUE (team_id, user_id)
);

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

-- Drop unique constraint
ALTER TABLE users DROP CONSTRAINT uq_email;

-- NULL behavior: SQL standard says NULLs are not equal, so multiple NULLs
-- are allowed in a UNIQUE column. (SQL Server <= 2019 allows only one NULL
-- unless filtered index is used.)

-- Basic foreign key
CREATE TABLE orders (
    id      SERIAL PRIMARY KEY,
    user_id INTEGER NOT NULL,
    FOREIGN KEY (user_id) REFERENCES users(id)
);

-- Inline (column-level) shorthand
CREATE TABLE orders (
    id      SERIAL PRIMARY KEY,
    user_id INTEGER NOT NULL REFERENCES users(id)
);

-- Named foreign key
CREATE TABLE orders (
    id      SERIAL,
    user_id INTEGER NOT NULL,
    CONSTRAINT fk_orders_user FOREIGN KEY (user_id) REFERENCES users(id)
);

-- Multi-column foreign key
CREATE TABLE order_items (
    id         SERIAL PRIMARY KEY,
    order_id   INTEGER NOT NULL,
    product_id INTEGER NOT NULL,
    CONSTRAINT fk_order_items_order
        FOREIGN KEY (order_id, product_id)
        REFERENCES order_products(order_id, product_id)
);

-- Add foreign key to existing table
ALTER TABLE orders
ADD CONSTRAINT fk_user FOREIGN KEY (user_id) REFERENCES users(id);

-- Drop foreign key
ALTER TABLE orders DROP CONSTRAINT fk_user;
ALTER TABLE orders DROP FOREIGN KEY fk_user;  -- MySQL syntax

-- Delete user → cascade-delete their orders
FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE

-- Delete category → set products.category_id to NULL
FOREIGN KEY (category_id) REFERENCES categories(id)
    ON DELETE SET NULL
    ON UPDATE CASCADE

-- Prevent deleting a user who has orders
FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE RESTRICT

-- Full example
CREATE TABLE comments (
    id        SERIAL PRIMARY KEY,
    post_id   INTEGER NOT NULL,
    parent_id INTEGER,
    body      TEXT NOT NULL,
    FOREIGN KEY (post_id)   REFERENCES posts(id)    ON DELETE CASCADE,
    FOREIGN KEY (parent_id) REFERENCES comments(id) ON DELETE CASCADE
);

-- Column-level check
CREATE TABLE products (
    price    DECIMAL(10,2) CHECK (price >= 0),
    discount DECIMAL(5,2)  CHECK (discount BETWEEN 0 AND 100)
);

-- Named check constraint
CREATE TABLE products (
    price DECIMAL(10,2),
    CONSTRAINT chk_price_positive CHECK (price >= 0)
);

-- Multi-column check (table-level)
CREATE TABLE discounts (
    start_date DATE NOT NULL,
    end_date   DATE NOT NULL,
    CONSTRAINT chk_dates CHECK (end_date > start_date)
);

-- Regex / IN pattern (for string enumeration)
CREATE TABLE users (
    status VARCHAR(20),
    CONSTRAINT chk_status CHECK (status IN ('active','inactive','banned','pending'))
);

-- Add check constraint to existing table
ALTER TABLE products ADD CONSTRAINT chk_qty CHECK (stock >= 0);

-- Drop check constraint
ALTER TABLE products DROP CONSTRAINT chk_qty;

-- Note: MySQL parses but historically ignores CHECK constraints before 8.0.16

-- Inline default
CREATE TABLE events (
    id         SERIAL PRIMARY KEY,
    title      TEXT NOT NULL,
    created_at TIMESTAMP    DEFAULT NOW(),
    is_public  BOOLEAN      DEFAULT TRUE,
    status     VARCHAR(20)  DEFAULT 'draft',
    views      INTEGER      DEFAULT 0
);

-- Named default (SQL Server)
CREATE TABLE events (
    status VARCHAR(20) CONSTRAINT df_status DEFAULT 'draft'
);

-- Add default to existing column
ALTER TABLE users ALTER COLUMN status SET DEFAULT 'active';       -- PostgreSQL
ALTER TABLE users ALTER COLUMN status SET DEFAULT 'active';       -- works in most
ALTER TABLE users MODIFY COLUMN status VARCHAR(20) DEFAULT 'active'; -- MySQL

-- Remove default
ALTER TABLE users ALTER COLUMN status DROP DEFAULT;

-- PostgreSQL: GENERATED ALWAYS AS ... STORED
CREATE TABLE employees (
    first_name TEXT NOT NULL,
    last_name  TEXT NOT NULL,
    full_name  TEXT GENERATED ALWAYS AS (first_name || ' ' || last_name) STORED
);

-- Cannot INSERT/UPDATE generated columns directly

-- MySQL 5.7+: GENERATED ALWAYS AS ... STORED or VIRTUAL
CREATE TABLE circles (
    radius DOUBLE NOT NULL,
    area   DOUBLE GENERATED ALWAYS AS (PI() * radius * radius) STORED,
    circumference DOUBLE GENERATED ALWAYS AS (2 * PI() * radius) VIRTUAL
);
-- STORED: computed once and persisted; VIRTUAL: recomputed on read (not stored)

-- SQL Server: AS ... PERSISTED
CREATE TABLE employees (
    first_name NVARCHAR(50),
    last_name  NVARCHAR(50),
    full_name  AS (first_name + ' ' + last_name) PERSISTED
);

-- List constraints on a table (PostgreSQL)
SELECT conname, contype, pg_get_constraintdef(oid)
FROM pg_constraint
WHERE conrelid = 'users'::regclass;

-- Information schema (standard, most DBs)
SELECT constraint_name, constraint_type
FROM information_schema.table_constraints
WHERE table_name = 'users';

-- Rename a constraint (PostgreSQL)
ALTER TABLE users RENAME CONSTRAINT uq_email TO uq_users_email;

-- Validate a previously NOT VALID constraint (PostgreSQL)
-- Add without scanning existing rows (fast on large tables):
ALTER TABLE orders ADD CONSTRAINT fk_user
    FOREIGN KEY (user_id) REFERENCES users(id)
    NOT VALID;
-- Then validate in background:
ALTER TABLE orders VALIDATE CONSTRAINT fk_user;

-- Disable / enable constraints (MySQL)
SET FOREIGN_KEY_CHECKS = 0;
-- ... bulk load ...
SET FOREIGN_KEY_CHECKS = 1;

-- Deferred constraints (PostgreSQL)
-- DEFERRABLE: can be deferred within a transaction
-- INITIALLY DEFERRED: deferred by default
-- INITIALLY IMMEDIATE: checked immediately by default (can be deferred per-transaction)
CREATE TABLE orders (
    id      SERIAL PRIMARY KEY,
    user_id INTEGER NOT NULL,
    CONSTRAINT fk_user FOREIGN KEY (user_id) REFERENCES users(id)
        DEFERRABLE INITIALLY DEFERRED
);

-- Defer within a transaction
BEGIN;
SET CONSTRAINTS fk_user DEFERRED;
-- ... do work that temporarily violates the FK ...
COMMIT;   -- FK checked here

-- Requires: CREATE EXTENSION IF NOT EXISTS btree_gist;

-- No two bookings for the same room overlap in time
CREATE TABLE bookings (
    room_id  INTEGER NOT NULL,
    during   TSRANGE NOT NULL,
    EXCLUDE USING GIST (room_id WITH =, during WITH &&)
);

-- &&: range overlap operator
-- =: equality