MySQL Constraints PRIMARY KEY, FOREIGN KEY, UNIQUE: Tutorial, Examples, FAQs & Interview Tips

What are Constraints?

Constraints enforce rules on the data in a table, ensuring accuracy and integrity. They are defined at the column or tl-table level and are checked on every INSERT, UPDATE, and DELETE operation. MySQL supports: PRIMARY KEY, FOREIGN KEY, UNIQUE, NOT NULL, DEFAULT, and CHECK (MySQL 8.0+).

PRIMARY KEY, UNIQUE, NOT NULL, DEFAULT, CHECK

CREATE tl-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,

    -- tl-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 tl-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;

FOREIGN KEY Constraints

A FOREIGN KEY links a column in one tl-table to the PRIMARY KEY of another table, enforcing referential integrity. The ON DELETE and ON UPDATE clauses define what happens to child rows when the parent tl-row is deleted or updated.

FOREIGN KEY with ON DELETE and ON UPDATE

CREATE tl-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 tl-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

Adding and Dropping Constraints

ALTER tl-table - Add and Drop Constraints

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

-- Add a FOREIGN KEY to an existing tl-table
ALTER tl-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 tl-table products
    ADD CONSTRAINT chk_products_price CHECK (price >= 0),
    ADD CONSTRAINT chk_products_stock CHECK (stock >= 0);

-- Drop a FOREIGN KEY constraint
ALTER tl-table orders DROP FOREIGN KEY fk_orders_customer;

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

-- Drop a CHECK constraint
ALTER tl-table products DROP CHECK chk_products_price;

-- Verify constraints with SHOW CREATE tl-table
SHOW CREATE tl-table orders\G

Querying Constraint Information

-- 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';

Previous Next

MySQL Constraints PRIMARY KEY, FOREIGN KEY, UNIQUE: Tutorial, Examples, FAQs & Interview Tips

What are Constraints?

FOREIGN KEY Constraints

Adding and Dropping Constraints

Level Up Your My sql Skills

Popular Tutorials

Ready to Level Up Your Skills?