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

MySQL Constraints PRIMARY KEY, FOREIGN KEY, UNIQUE

MySQL in MySQL is best learned by connecting the rule to an order-management database. Start with the smallest SQL statement, observe the output, and then add one realistic constraint so the concept becomes practical.

The key habit for this lesson is to watch table rows, constraints, and indexes as it changes. That makes the topic easier to debug, easier to explain in interviews, and easier to use in real code without memorizing isolated syntax.

What are Constraints?

Constraints enforce rules on the data in a table, ensuring accuracy and integrity. They are defined at the column or 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 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;

FOREIGN KEY Constraints

A FOREIGN KEY links a column in one 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 row is deleted or updated.

FOREIGN KEY with ON DELETE and ON UPDATE

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

Adding and Dropping Constraints

ALTER table - Add and Drop Constraints

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

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

Applied guide for MySQL

Use MySQL when the program needs a clear answer to a specific problem, not because the keyword looks familiar. In a real MySQL task, first name the input, then name the transformation, then name the output. This small discipline shows whether the topic is being used correctly or only copied from an example.

A reliable practice flow is: create the smallest working SQL statement, add one normal case, add one edge case such as missing, repeated, empty, or boundary input, and then confirm the result with result set and EXPLAIN output. If the result surprises you, reduce the code until the behavior is visible again.

The most common trap here is copying the syntax before understanding the behavior. Avoid it by writing one sentence before the code that explains why MySQL is the right choice. After the code runs, verify the lesson by doing this: change one input and explain the changed output.

Identify the exact problem solved by MySQL.
Trace table rows, constraints, and indexes before and after the main operation.
Keep one intentionally broken version and explain the fix.
Connect the example to an order-management database so the idea feels concrete.

Key Takeaways

I can explain where MySQL fits inside an order-management database.
I can point to the exact table rows, constraints, and indexes affected by this topic.
I tested a normal case and an edge case involving missing, repeated, empty, or boundary input.
I verified the result with result set and EXPLAIN output instead of assuming it worked.
I can describe the main mistake: copying the syntax before understanding the behavior.

Common Mistakes to Avoid

WRONG Copying the syntax before understanding the behavior.

RIGHT Write the expected behavior first, then make the example prove it.

A one-line expectation turns the code from copied syntax into a testable idea.

WRONG Practicing only the perfect input.

RIGHT Also test missing, repeated, empty, or boundary input before considering the lesson complete.

The edge case is where most interview follow-up questions begin.

WRONG Looking only at the final output.

RIGHT Trace table rows, constraints, and indexes through each important step.

Tracing makes debugging faster because you can see the first incorrect state.

Practice Tasks

Build one small SQL statement that demonstrates MySQL in an order-management database.
Change the example to include missing, repeated, empty, or boundary input and record the difference.
Break the example by deliberately copying the syntax before understanding the behavior, then write the corrected version.
Explain the finished example in five bullet points: input, operation, output, failure case, and verification.

Frequently Asked Questions

When should I use MySQL?

Use it when the problem matches the behavior shown in the example and when the result can be verified through result set and EXPLAIN output.

How do I avoid mistakes in MySQL?

Start with a tiny case, then test missing, repeated, empty, or boundary input. The main warning sign is copying the syntax before understanding the behavior.

How can I revise MySQL quickly?

Trace table rows, constraints, and indexes, predict the result, run the example, and compare your prediction with the actual output.

Previous Next

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