SQL Constraints: Primary Key, Foreign Key, UNIQUE and CHECK

Think of the membership desk at a city library in Indore. Every member gets a card with a number that no one else will ever have. When you borrow a book, the issue slip must carry a card number, and the desk checks that the card is real before the book leaves.

There are more rules: no two members share a phone number, a slip cannot say minus two books, and a form with no branch assumes the main branch.

Those rules are constraints. The card number is a primary key, the card number on each slip is a foreign key, the phone rule is UNIQUE, the "no negative books" rule is a CHECK, and the main-branch assumption is a DEFAULT.

SQL constraints are rules defined on columns or tables that the database checks on every INSERT and UPDATE, rejecting any change that would break them.

You first declared constraints in the CREATE TABLE article. This article, the first in the Database Design part of the series, goes deeper: choosing keys, composite keys, deleting parent rows and database differences.

What are the main types of SQL constraints?

A column constraint sits next to one column; a table constraint is a separate line, needed when a rule covers several columns.

What is a primary key in SQL?

A primary key is the column, or set of columns, that identifies each row. It must be unique and never NULL. Without one, you cannot reliably update or delete exactly one row.

There are two ways to choose it:

• Surrogate key: a meaningless number the database assigns, such as order_id. It never changes, which makes it ideal for joins.
• Natural key: a real-world value, such as a phone number or GSTIN. It means something to people, but real-world values change. Customers switch phone numbers.

The usual design is a surrogate primary key plus a UNIQUE constraint on the natural key. Malwa Mart identifies customers by customer_id and stops duplicate sign-ups with UNIQUE (phone).

Composite primary keys

Some tables are identified by a combination of columns. A bill can contain several products, so Malwa Mart stores bill lines in an order_items table. Order 101, for 1,040 rupees, has four lines: one each of basmati rice, detergent, sev and poha.

Neither order_id nor product_id is unique on its own here, but the pair is: a product appears at most once per bill. So the primary key is PRIMARY KEY (order_id, product_id).

CREATE TABLE order_items (
    order_id   INTEGER NOT NULL REFERENCES orders(order_id) ON DELETE CASCADE,
    product_id INTEGER NOT NULL REFERENCES products(product_id),
    quantity   INTEGER NOT NULL CHECK (quantity > 0),
    PRIMARY KEY (order_id, product_id)
);

What is a foreign key in SQL?

A foreign key is a column whose values must match the primary key (or a unique column) of another table. The table with the foreign key is the child; the table it points to is the parent. The guarantee is called referential integrity: no order can point to a customer who does not exist.

Arrows run from each foreign key to the primary key it references. order_items has a two-column primary key, and each part is also a foreign key.

Foreign keys make SQL joins trustworthy: if every order has a real customer, an INNER JOIN never silently drops an order.

Watching the database reject bad data

The block below builds the products and order_items tables with constraints, adds order 101's four lines, and then tries six bad inserts. Each one breaks a different rule.

import sqlite3

con = sqlite3.connect(":memory:")
con.execute("PRAGMA foreign_keys = ON")          # SQLite needs this on every connection
con.executescript("""
CREATE TABLE products (product_id INTEGER PRIMARY KEY, product_name TEXT NOT NULL UNIQUE,
    category TEXT NOT NULL, price INTEGER NOT NULL CONSTRAINT price_positive CHECK (price > 0));
CREATE TABLE order_items (order_id INTEGER NOT NULL,
    product_id INTEGER NOT NULL REFERENCES products(product_id),
    quantity INTEGER NOT NULL CHECK (quantity > 0), PRIMARY KEY (order_id, product_id));
INSERT INTO products VALUES (1,'Basmati Rice 5kg','Grocery',520), (3,'Poha 1kg','Grocery',60),
 (7,'Detergent 2kg','Household',340), (8,'Sev 500g','Snacks',120);
INSERT INTO order_items VALUES (101,1,1), (101,7,1), (101,8,1), (101,3,1);
""")
attempts = {
    "rice twice on bill 101":  "INSERT INTO order_items VALUES (101, 1, 2)",
    "product 2 not in table":  "INSERT INTO order_items VALUES (102, 2, 2)",
    "zero quantity":           "INSERT INTO order_items VALUES (104, 3, 0)",
    "duplicate product name":  "INSERT INTO products VALUES (9, 'Sev 500g', 'Snacks', 120)",
    "free sample at price 0":  "INSERT INTO products VALUES (9, 'Free sample', 'Promo', 0)",
    "no category given":       "INSERT INTO products (product_id, product_name, price) VALUES (9, 'Ghee 1L', 650)",
}
for label, sql in attempts.items():
    try:
        con.execute(sql)
        print(f"accepted  {label}")
    except sqlite3.IntegrityError as err:
        print(f"rejected  {label:24} -> {err}")

All six are rejected, and each message names the rule that failed. The named constraint price_positive appears by name, which is why teams name their CHECKs. Product 2 (Toor Dal) was left out on purpose, so the foreign key catches it.

What happens to child rows on DELETE?

A foreign key also decides what happens when someone deletes a parent row that children still point to. You choose the behaviour with ON DELETE (and similarly ON UPDATE).

RESTRICT protects history, CASCADE removes rows that make no sense alone, and SET NULL keeps the child but forgets the link.

NO ACTION is the default. It differs from RESTRICT only in timing: NO ACTION may be checked at the end of a statement or transaction, RESTRICT immediately.

The block below uses the staff table from the self join article, where manager_id points back into the same table.

import sqlite3

con = sqlite3.connect(":memory:")
con.execute("PRAGMA foreign_keys = ON")
con.executescript("""
CREATE TABLE stores (store_id INTEGER PRIMARY KEY, store_name TEXT);
CREATE TABLE orders (order_id INTEGER PRIMARY KEY, store_id INTEGER REFERENCES stores(store_id) ON DELETE RESTRICT);
CREATE TABLE order_items (order_id INTEGER REFERENCES orders(order_id) ON DELETE CASCADE,
                          product_id INTEGER, quantity INTEGER, PRIMARY KEY (order_id, product_id));
CREATE TABLE staff (staff_id INTEGER PRIMARY KEY, name TEXT,
                    manager_id INTEGER REFERENCES staff(staff_id) ON DELETE SET NULL);
INSERT INTO stores VALUES (1,'Malwa Mart Vijay Nagar'), (4,'Malwa Mart Bhawarkuan');
INSERT INTO orders VALUES (101,1), (106,4), (109,4);
INSERT INTO order_items VALUES (101,1,1), (101,7,1), (101,8,1), (101,3,1), (106,8,1), (109,7,1);
INSERT INTO staff VALUES (1,'Rajesh Sharma',NULL), (3,'Arjun Malviya',1), (5,'Deepak Yadav',3);
""")
try:
    con.execute("DELETE FROM stores WHERE store_id = 4")
except sqlite3.IntegrityError as err:
    print("RESTRICT: store 4 not deleted ->", err)

con.execute("DELETE FROM orders WHERE order_id = 101")
print("CASCADE : items left =", con.execute("SELECT COUNT(*) FROM order_items").fetchone()[0], "(was 6)")

con.execute("DELETE FROM staff WHERE staff_id = 3")
print("SET NULL:", con.execute("SELECT name, manager_id FROM staff WHERE staff_id = 5").fetchone())

Bhawarkuan cannot be deleted while orders 106 and 109 point to it. Deleting order 101 takes its four bill lines with it. When Arjun Malviya leaves, Deepak Yadav stays on with no manager until one is assigned.

Constraint differences in SQLite, MySQL and PostgreSQL

• Foreign keys in SQLite are off by default. Run PRAGMA foreign_keys = ON on every new connection, or they are silently ignored. MySQL's InnoDB engine and PostgreSQL always enforce them.
• CHECK in MySQL. MySQL parsed CHECK constraints but ignored them before version 8.0.16. On older servers, a CHECK gives you no protection.
• NULL in a SQLite primary key. Because of a long-standing quirk, SQLite allows NULL in a non-integer primary key column unless you also write NOT NULL or use a STRICT or WITHOUT ROWID table.
• Adding constraints later. MySQL and PostgreSQL support ALTER TABLE orders ADD CONSTRAINT fk_customer FOREIGN KEY (customer_id) REFERENCES customers(customer_id). SQLite cannot add constraints to an existing table; you rebuild the table, as the CREATE TABLE article shows.

Before adding a foreign key to old data, look for orphans: child rows whose parent is missing. A LEFT JOIN with WHERE parent.id IS NULL finds them, and SQLite also offers PRAGMA foreign_key_check.

import sqlite3

con = sqlite3.connect(":memory:")
con.executescript("""
CREATE TABLE customers (customer_id INTEGER PRIMARY KEY, name TEXT);
CREATE TABLE orders (order_id INTEGER PRIMARY KEY, customer_id INTEGER REFERENCES customers(customer_id));
INSERT INTO customers VALUES (1,'Asha Verma'), (2,'Ravi Patel'), (3,'Meera Joshi');
INSERT INTO orders VALUES (101,1), (102,2), (104,3), (190,99), (191,42);   -- two legacy orphans
""")
orphans = con.execute("""
    SELECT o.order_id, o.customer_id FROM orders o
    LEFT JOIN customers c ON c.customer_id = o.customer_id
    WHERE c.customer_id IS NULL""").fetchall()
print("orphans via LEFT JOIN:", orphans)
print("PRAGMA foreign_key_check:", con.execute("PRAGMA foreign_key_check").fetchall())

Orders 190 and 191 point to missing customers. Fix or remove them first, or MySQL and PostgreSQL will refuse to create the constraint.

Common mistakes with SQL constraints and keys

• No primary key at all. Duplicate rows creep in and you cannot target a single row safely.
• Using a changeable value as the primary key. Phone numbers and email addresses change; put them under UNIQUE instead.
• Forgetting PRAGMA foreign_keys in SQLite. The schema looks safe, but nothing is checked.
• Cascading deletes on business history. Deleting one customer should not erase a year of sales.
• Validating only in the app. A second app, a script or a bulk import will bypass it. Constraints protect the data no matter who writes it.

Interviewers often ask candidates to sketch a schema. To practise that with feedback, database design lessons in Debugshala's analytics course build schemas like this one, and database design quizzes on Debugshala Test are a quick revision. Next, database normalisation explains how to decide which tables you need in the first place.

Key takeaways

• Constraints move data rules into the database, where every app and script must obey them.
• A primary key is unique and not NULL; use a surrogate key and protect natural keys with UNIQUE.
• A composite primary key such as (order_id, product_id) identifies rows by a combination of columns.
• A foreign key guarantees that a referenced row exists; ON DELETE decides what happens to children.
• In SQLite, turn on PRAGMA foreign_keys and remember you cannot add constraints with ALTER TABLE.