MySQL Constraints

CREATE TABLE customers (
    customer_id  INT           UNSIGNED NOT NULL AUTO_INCREMENT,
    first_name   VARCHAR(50)   NOT NULL,                          -- NOT NULL
    last_name    VARCHAR(50)   NOT NULL,
    email        VARCHAR(255)  NOT NULL,
    phone        VARCHAR(20),                                     -- nullable
    active       TINYINT(1)    NOT NULL DEFAULT 1,                -- DEFAULT
    created_at   DATETIME      NOT NULL DEFAULT CURRENT_TIMESTAMP,

    -- Table-level PRIMARY KEY
    PRIMARY KEY (customer_id),

    -- UNIQUE constraint: no two customers can share an email
    UNIQUE KEY uq_customers_email (email),

    -- CHECK constraint (MySQL 8.0+): enforce valid data
    CONSTRAINT chk_customers_active CHECK (active IN (0, 1))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Composite PRIMARY KEY (for junction/pivot tables)
CREATE TABLE product_tags (
    product_id INT UNSIGNED NOT NULL,
    tag_id     INT UNSIGNED NOT NULL,
    PRIMARY KEY (product_id, tag_id)   -- composite primary key
) ENGINE=InnoDB;

CREATE TABLE orders (
    order_id    INT UNSIGNED NOT NULL AUTO_INCREMENT,
    customer_id INT UNSIGNED NOT NULL,
    total       DECIMAL(10,2) NOT NULL DEFAULT 0.00,
    status      ENUM('pending','processing','shipped','delivered','cancelled')
                NOT NULL DEFAULT 'pending',
    ordered_at  DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (order_id),

    -- FOREIGN KEY: orders.customer_id references customers.customer_id
    CONSTRAINT fk_orders_customer
        FOREIGN KEY (customer_id) REFERENCES customers (customer_id)
        ON DELETE RESTRICT    -- prevent deleting a customer who has orders
        ON UPDATE CASCADE     -- if customer_id changes, update orders too
) ENGINE=InnoDB;

CREATE TABLE order_items (
    item_id    INT UNSIGNED NOT NULL AUTO_INCREMENT,
    order_id   INT UNSIGNED NOT NULL,
    product_id INT UNSIGNED NOT NULL,
    quantity   INT UNSIGNED NOT NULL DEFAULT 1,
    unit_price DECIMAL(10,2) NOT NULL,
    PRIMARY KEY (item_id),

    CONSTRAINT fk_items_order
        FOREIGN KEY (order_id) REFERENCES orders (order_id)
        ON DELETE CASCADE,    -- deleting an order also deletes its items

    CONSTRAINT fk_items_product
        FOREIGN KEY (product_id) REFERENCES products (product_id)
        ON DELETE RESTRICT    -- prevent deleting a product that has been ordered
) ENGINE=InnoDB;

-- Foreign key actions:
-- RESTRICT  : prevent the parent delete/update if child rows exist
-- CASCADE   : automatically delete/update child rows
-- SET NULL  : set child FK column to NULL (column must be nullable)
-- NO ACTION : same as RESTRICT in MySQL

-- Add a UNIQUE constraint to an existing table
ALTER TABLE customers
    ADD CONSTRAINT uq_customers_phone UNIQUE (phone);

-- Add a FOREIGN KEY to an existing table
ALTER TABLE orders
    ADD CONSTRAINT fk_orders_customer
        FOREIGN KEY (customer_id) REFERENCES customers (customer_id)
        ON DELETE RESTRICT ON UPDATE CASCADE;

-- Add a CHECK constraint (MySQL 8.0+)
ALTER TABLE products
    ADD CONSTRAINT chk_products_price CHECK (price >= 0),
    ADD CONSTRAINT chk_products_stock CHECK (stock >= 0);

-- Drop a FOREIGN KEY constraint
ALTER TABLE orders DROP FOREIGN KEY fk_orders_customer;

-- Drop a UNIQUE constraint (drop the index)
ALTER TABLE customers DROP INDEX uq_customers_phone;

-- Drop a CHECK constraint
ALTER TABLE products DROP CHECK chk_products_price;

-- Verify constraints with SHOW CREATE TABLE
SHOW CREATE TABLE orders\G

-- List all foreign keys in the shop database
SELECT
    TABLE_NAME,
    CONSTRAINT_NAME,
    COLUMN_NAME,
    REFERENCED_TABLE_NAME,
    REFERENCED_COLUMN_NAME
FROM information_schema.KEY_COLUMN_USAGE
WHERE TABLE_SCHEMA = 'shop'
  AND REFERENCED_TABLE_NAME IS NOT NULL;

-- List all CHECK constraints (MySQL 8.0+)
SELECT
    CONSTRAINT_NAME,
    TABLE_NAME,
    CHECK_CLAUSE
FROM information_schema.CHECK_CONSTRAINTS
WHERE CONSTRAINT_SCHEMA = 'shop';