Constraints (PRIMARY KEY, FOREIGN KEY, UNIQUE, CHECK)

Reviewed & published by Brayan K

Constraints are the rules your database enforces so bad data can never get in — no duplicate ids, no negative prices, no orders pointing at customers who don't exist. They're the difference between a database you can trust and a pile of unreliable rows.

Part of the free SQL course at LearnCodingFast — hands-on lessons with examples you run in your browser, plus practice exercises and a quick quiz.

By the end you'll wield all the core constraints — PRIMARY KEY, UNIQUE, NOT NULL, CHECK, and FOREIGN KEY — and understand referential integrity and ON DELETE behaviour.

What You'll Learn

• Give rows a unique identity with PRIMARY KEY
• Forbid duplicates with UNIQUE
• Enforce business rules with CHECK
• Link tables with FOREIGN KEY
• Keep referential integrity (no orphan rows)
• Control deletes with ON DELETE CASCADE / SET NULL

The Five Core Constraints

Each constraint enforces a different guarantee. Keep this map in mind as you read.

1. PRIMARY KEY — Identity

A PRIMARY KEY uniquely identifies every row. It's automatically UNIQUE and NOT NULL, and a table can have exactly one (though it may span multiple columns — a "composite" key).

A primary key is like a passport number: exactly one per person, never blank, and never shared. It's how the system tells one row apart from every other.

-- PRIMARY KEY: a unique, never-NULL identity for each row.
DROP TABLE IF EXISTS customers;
CREATE TABLE customers (
    id    INTEGER PRIMARY KEY,
    email VARCHAR(255),
    name  VARCHAR(100)
);

INSERT INTO customers VALUES (1, 'ava@x.com', 'Ava');

-- The second row reuses id 1, so the primary key rejects it.
-- "OR IGNORE" tells the engine to drop just the offending row instead of
-- stopping everything, which is what lets you SEE the rejection below.
-- Without it you would get: UNIQUE constraint failed: customers.id
INSERT OR IGNORE INTO customers VALUES (1, 'ben@x.com', 'Ben');

-- Ben never landed. One row, and it is still Ava's:
SELECT * FROM customers ORDER BY id;

-- A composite key uses two columns together:
--   PRIMARY KEY (order_id, product_id)

-- ✅ Expected result:
-- id | email | name
-- 1 | ava@x.com | Ava

2. UNIQUE — No Duplicates

UNIQUE forbids two rows from sharing a value, like an email address. Unlike a primary key, you can have several UNIQUE columns, and they usually permit NULL.

-- UNIQUE: no two rows may share the value (but NULLs are usually allowed).
DROP TABLE IF EXISTS customers;
CREATE TABLE customers (
    id    INTEGER PRIMARY KEY,
    email VARCHAR(255) UNIQUE,     -- every email must be different
    name  VARCHAR(100)
);

INSERT INTO customers VALUES (1, 'ava@x.com', 'Ava');

-- A different id, but the same email — UNIQUE rejects it:
INSERT OR IGNORE INTO customers VALUES (2, 'ava@x.com', 'Alt');

-- NULL is not a value, so several rows may be NULL here:
INSERT INTO customers VALUES (3, NULL, 'Cara');
INSERT INTO customers VALUES (4, NULL, 'Dev');

SELECT * FROM customers ORDER BY id;

-- PRIMARY KEY = UNIQUE + NOT NULL, and there can be only one per table.
-- You can have MANY UNIQUE columns.

-- ✅ Expected result:
-- id | email | name
-- 1 | ava@x.com | Ava
-- 3 | NULL | Cara
-- 4 | NULL | Dev

3. NOT NULL & CHECK — Value Rules

NOT NULL requires a value; CHECK enforces a custom condition on every insert and update. Together they keep impossible data — a missing name, a negative price — out of the table.

-- NOT NULL forbids empty values. CHECK enforces a custom rule.
DROP TABLE IF EXISTS products;
CREATE TABLE products (
    id     INTEGER PRIMARY KEY,
    name   VARCHAR(120) NOT NULL,            -- must be provided
    price  DECIMAL(8,2) NOT NULL
           CHECK (price >= 0),               -- no negative prices
    stock  INTEGER DEFAULT 0
           CHECK (stock >= 0)
);

INSERT INTO products VALUES (1, 'Mug', 9.50, 12);

-- Both of these break a rule, so both are dropped:
INSERT OR IGNORE INTO products VALUES (2, 'Bad',  -5.00, 0);  -- CHECK (price >= 0)
INSERT OR IGNORE INTO products VALUES (3, NULL,    4.00, 1);  -- NOT NULL (name)

-- Only the Mug is here. The constraints did the arguing for you:
SELECT * FROM products ORDER BY id;

-- ✅ Expected result:
-- id | name | price | stock
-- 1 | Mug | 9.5 | 12

4. FOREIGN KEY — Linking Tables

A FOREIGN KEY ties a column to a key in another table, so an order's customer_id must match a real customer. This is referential integrity — the database refuses to create orphaned rows.

-- FOREIGN KEY links a column to a key in another table.
-- SQLite only ENFORCES foreign keys when you ask it to, and this site's
-- editor is SQLite. Without the next line the bad row below is accepted,
-- which is a real gotcha worth meeting once:
PRAGMA foreign_keys = ON;

DROP TABLE IF EXISTS orders;
CREATE TABLE orders (
    id          INTEGER PRIMARY KEY,
    customer_id INTEGER NOT NULL,
    total       DECIMAL(8,2),
    FOREIGN KEY (customer_id) REFERENCES customers(id)
);

-- Uses the customers table from the block above, which has ids 1, 3 and 4.
INSERT INTO orders VALUES (101, 1, 42.00);   -- customer 1 exists

-- There is no customer 99, so this one is refused outright:
--   INSERT INTO orders VALUES (102, 99, 10.00);
--   ❌ FOREIGN KEY constraint failed
-- It is commented out because, unlike the CHECK and UNIQUE cases above,
-- "OR IGNORE" does NOT rescue a foreign-key violation — SQLite treats
-- those as an error rather than a conflict to skip, so the whole block
-- would stop here. Uncomment it and see for yourself.

SELECT * FROM orders ORDER BY id;

-- This is "referential integrity": orders can't point at ghosts.

-- ✅ Expected result:
-- id | customer_id | total
-- 101 | 1 | 42

Your Turn: a reviews table

Fill in the constraint keywords so ratings stay between 1 and 5 and each review links to a real product.

-- 🎯 YOUR TURN — fill in the blanks
-- Goal: a "reviews" table where rating must be 1 to 5, and each
-- review links to an existing product. Uses the products table above,
-- which holds exactly one row: the Mug, id 1.
PRAGMA foreign_keys = ON;
DROP TABLE IF EXISTS reviews;

CREATE TABLE reviews (
    id         INTEGER PRIMARY KEY,
    product_id INTEGER NOT NULL,
    rating     INTEGER ___ (rating BETWEEN 1 AND 5),  -- 👉 replace ___ with CHECK
    FOREIGN KEY (product_id) ___ products(id)         -- 👉 replace ___ with REFERENCES
);

INSERT INTO reviews VALUES (1, 1, 5);            -- fine
INSERT OR IGNORE INTO reviews VALUES (2, 1, 7);  -- rating out of range: refused

-- A review of a product that does not exist is refused too, but a
-- foreign-key failure stops the block rather than skipping the row,
-- so try this one on its own:
--   INSERT INTO reviews VALUES (3, 99, 4);
--   ❌ FOREIGN KEY constraint failed

-- Get both blanks right and only the first row is here:
SELECT * FROM reviews ORDER BY id;

-- ✅ Expected result:
-- id | product_id | rating
-- 1 | 1 | 5

5. ON DELETE — Cascades and Cleanup

What happens to an order when its customer is deleted? ON DELETE decides: CASCADE deletes the children too, SET NULL orphans them gracefully, and RESTRICT blocks the delete while children exist.

-- ON DELETE controls what happens to child rows when a parent is deleted.
PRAGMA foreign_keys = ON;
DROP TABLE IF EXISTS orders;
CREATE TABLE orders (
    id          INTEGER PRIMARY KEY,
    customer_id INTEGER,
    FOREIGN KEY (customer_id)
        REFERENCES customers(id)
        ON DELETE CASCADE      -- delete the customer -> their orders go too
);

INSERT INTO orders VALUES (101, 1), (102, 1), (103, 3);

-- Now remove customer 1. CASCADE takes both of their orders with them,
-- without you naming those orders anywhere:
DELETE FROM customers WHERE id = 1;

SELECT * FROM orders ORDER BY id;

-- Other options:
--   ON DELETE RESTRICT  -> block the delete if children exist (default-ish)
--   ON DELETE SET NULL  -> keep the order, set customer_id to NULL

-- ✅ Expected result:
-- id | customer_id
-- 103 | 3

6. Naming Your Constraints

Give constraints explicit names with CONSTRAINT name. Error messages become readable (chk_balance instead of products_check1), and you can drop them by name later.

-- Naming constraints makes errors readable and lets you drop them later.
DROP TABLE IF EXISTS accounts;
CREATE TABLE accounts (
    id      INTEGER PRIMARY KEY,
    balance DECIMAL(10,2)
            CONSTRAINT chk_balance CHECK (balance >= 0),
    email   VARCHAR(255)
            CONSTRAINT uq_email UNIQUE
);

INSERT INTO accounts VALUES (1, 250.00, 'ava@x.com');
INSERT OR IGNORE INTO accounts VALUES (2, -10.00, 'ben@x.com');  -- chk_balance
INSERT OR IGNORE INTO accounts VALUES (3, 40.00, 'ava@x.com');   -- uq_email

SELECT * FROM accounts ORDER BY id;

-- A violation now reports "chk_balance" by name instead of a generated one,
-- and a named constraint can be dropped later:
--   ALTER TABLE accounts DROP CONSTRAINT chk_balance;

-- ✅ Expected result:
-- id | balance | email
-- 1 | 250 | ava@x.com

Reorder Challenge

You're inserting data into two related tables. Order these so the foreign key never fails:

A.   INSERT INTO orders VALUES (101, 1, 42.00);
B.   CREATE TABLE customers (id INTEGER PRIMARY KEY, name VARCHAR(50));
C.   CREATE TABLE orders (id INTEGER PRIMARY KEY, customer_id INTEGER REFERENCES customers(id), total DECIMAL(8,2));
D.   INSERT INTO customers VALUES (1, 'Ava');

Quick Recall

1) How many PRIMARY KEYs can a table have? How many UNIQUE constraints?

Exactly one PRIMARY KEY, but as many UNIQUE constraints as you like.

2) You insert an order with customer_id = 99 but no such customer exists. What happens?

The FOREIGN KEY rejects the insert — referential integrity won't allow an order pointing at a non-existent customer.

3) Which constraint stops a rating of 7 in a 1–5 scale?

A CHECK (rating BETWEEN 1 AND 5) constraint rejects any value outside the range.

Common Errors (and the fix)

• "foreign key constraint fails": the referenced parent row doesn't exist. Insert the parent first.
• "duplicate key value": you inserted a value that violates a PRIMARY KEY or UNIQUE rule.
• Dropping a referenced parent row: blocked unless you use ON DELETE CASCADE/SET NULL.
• CHECK ignored: very old MySQL parsed but didn't enforce CHECK — use 8.0.16+ or another engine.
• Two primary keys: you can't declare PRIMARY KEY twice; combine columns into one composite key.

Mini-Challenge: An Enrollments Table

Use all four constraint types in one table. Write it, then run it to confirm it's valid.

-- 🎯 MINI-CHALLENGE
-- Create an "enrollments" table that:
--   - has its own integer id PRIMARY KEY
--   - links student_id to a students(id) FOREIGN KEY
--   - links course_id to a courses(id) FOREIGN KEY
--   - has a "grade" CHECK that it is between 0 and 100
--   - the SAME student can't enrol in the SAME course twice
--     (hint: a UNIQUE constraint across two columns)
--
-- ✅ Expected: a valid CREATE TABLE with all four constraint types.

-- your CREATE TABLE here

🎉 Lesson Complete

• ✅ PRIMARY KEY = unique + NOT NULL identity (one per table)
• ✅ UNIQUE forbids duplicates; NOT NULL forbids blanks
• ✅ CHECK enforces custom rules like price >= 0
• ✅ FOREIGN KEY links tables and guarantees referential integrity
• ✅ ON DELETE CASCADE / SET NULL / RESTRICT controls cleanup
• ✅ Next: DROP vs TRUNCATE vs DELETE — removing data and tables

Practice quiz

Continue this course

• Previous: ALTER TABLE
• Next: DROP vs TRUNCATE vs DELETE — The three ways to remove data — and when to use each
• Quick reference: SQL cheat sheet › Tables & Schema
• From the blog: Database Indexing Strategies · SQL Database Design Best Practices

Frequently asked questions

What's the difference between a PRIMARY KEY and a UNIQUE constraint?

Both guarantee no duplicate values, but a PRIMARY KEY is also implicitly NOT NULL and a table can have only one of them — it's the row's official identity. A UNIQUE constraint allows NULLs (usually) and you can have several per table. Use PRIMARY KEY for the identity column and UNIQUE for other fields that must not repeat, like an email.

What does a FOREIGN KEY actually enforce?

Referential integrity: it guarantees that a value in the child column matches an existing key in the parent table. You can't insert an order for customer_id 99 if no customer 99 exists, and (depending on ON DELETE rules) you can't delete a customer who still has orders. It keeps relationships between tables consistent and prevents orphaned rows.

When should I use a CHECK constraint?

Use CHECK to enforce a business rule on the data itself — price >= 0, rating BETWEEN 1 AND 5, status IN ('open','closed'). It runs on every INSERT and UPDATE and rejects rows that violate the rule. It's a database-level safety net so bad data can't get in even if application code has a bug.

What does ON DELETE CASCADE do?

It tells the database to automatically delete child rows when their parent is deleted. Delete a customer with ON DELETE CASCADE and all their orders vanish too. Alternatives are ON DELETE RESTRICT (block the delete while children exist) and ON DELETE SET NULL (keep the child but null out the foreign key). Choose based on whether child data should survive its parent.

Related lessons

• CREATE TABLE