SQL Constraints & Keys: Primary, Foreign, Unique & Check

Imagine your college's admission register with no rules: two students sharing the same roll number, a student with no name, an age entered as −5, and a hostel room assigned to a student who was never admitted. Every query you learned in lessons 1–6 assumes the data is sane — constraints are how the data stays sane. They are rules the table enforces on itself, automatically, on every INSERT and UPDATE, forever. This lesson is about the rules — and about keys, the most important rule of all.

PRIMARY KEY — every row gets an identity

A primary key is a column (or a group of columns) that uniquely identifies each row — a roll number for data. It bundles two promises: UNIQUE (no two rows share a value) and NOT NULL (identity can never be blank). One per table.

CREATE TABLE students (
    student_id INT PRIMARY KEY,
    name VARCHAR(100) NOT NULL
);

Two bonus facts that upgrade your answers: the engine automatically builds an index on the primary key (lookups by it are fast for free), and in MySQL's InnoDB the table's rows are physically stored in primary-key order — the clustered index you'll meet properly in the indexing lesson.

Key vocabulary — five words, one ladder

Interviewers love this ladder, so climb it once and own it. In a students table where both roll_no and email are unique:

• Super key — any column set that uniquely identifies a row, extra baggage allowed: (roll_no), (email), (roll_no, name)…
• Candidate key — a minimal super key: (roll_no) and (email). Candidates for the throne.
• Primary key — the one candidate you crown: (roll_no). The others typically become UNIQUE constraints.
• Composite key — a key needing two or more columns together, like (order_id, product_id) in an order-items table.
• Surrogate key — an artificial auto-increment ID invented purely to be the key, when no natural column is trustworthy.

UNIQUE vs PRIMARY KEY — the three differences

Both forbid duplicates, so what's different? One: one PK per table, but as many UNIQUE constraints as you like. Two: a PK never holds NULL; a UNIQUE column (in MySQL and ANSI SQL) can hold many NULLs — because NULL never equals NULL, two blanks never "collide". Three-valued logic from lesson 1, paying rent again. Three: the PK is the conventional target for foreign keys pointing at this table.

NOT NULL, CHECK, DEFAULT — the small guards

CREATE TABLE students (
    student_id INT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,             -- mandatory
    email VARCHAR(150) UNIQUE,              -- no duplicates
    age INT CHECK (age >= 16),              -- domain rule
    status VARCHAR(20) DEFAULT 'active'     -- fallback value
);

NOT NULL forbids blanks. CHECK attaches any boolean rule — CHECK (age >= 16), CHECK (end_date > start_date), CHECK (status IN ('pending','shipped')) — things NOT NULL and UNIQUE can't express. DEFAULT fills a value in when the INSERT stays silent. Together they push data-quality rules out of application code (where someone forgets them) into the table itself (where nobody can).

FOREIGN KEY — no pointing at ghosts

You met the idea in lesson 2: orders.customer_id points at customers.customer_id. The FOREIGN KEY constraint makes that pointer enforced:

ALTER TABLE orders
  ADD CONSTRAINT fk_orders_customer
  FOREIGN KEY (customer_id) REFERENCES customers(customer_id);

From now on: inserting an order with a customer_id that doesn't exist → rejected. Deleting a customer who still has orders → rejected (by default). That guarantee — no child row ever points at a missing parent — is referential integrity. Unlike PKs, a table can have many FKs, FK values can repeat (one customer, many orders), and an FK can be NULL, meaning "this child has no parent assigned".

ON DELETE — deciding the children's fate

The interesting design question: when a parent dies, what happens to its children? You choose, per foreign key:

One caution before you fall in love with CASCADE: on a self-referencing FK (employees.manager_id → employees.emp_id), a cascade follows the hierarchy — delete one manager and their entire reporting subtree silently vanishes. For hierarchies, SET NULL or RESTRICT is almost always the wiser default.

Common mistakes

• Saying "UNIQUE and PRIMARY KEY are the same" — the NULL behaviour and one-per-table rule differ (and get asked).
• Forgetting that MySQL UNIQUE columns can hold multiple NULLs.
• Choosing CASCADE everywhere "for convenience" — one DELETE can quietly erase far more than intended.
• Trying to express "salary must be positive" with NOT NULL — that's a CHECK constraint's job.
• Adding a separate UNIQUE constraint on (order_id, product_id) when the composite PRIMARY KEY already guarantees it.

Quick recap

Practice Zone — PYQs from real selection rounds

Design-flavoured questions this time: you'll write CREATE TABLE and ALTER TABLE statements the way schema reviews actually go.

Q1Primary keyeasy

Which two properties does a PRIMARY KEY combine?

Asked in

AUNIQUE and NOT NULLBUNIQUE and DEFAULTCNOT NULL and CHECKDINDEX and FOREIGN KEY

Q2PK vs UNIQUEmedium

In MySQL, how many NULLs can a column with a UNIQUE constraint hold?

Asked in

ANone — UNIQUE implies NOT NULLBExactly oneCMany — NULL is never equal to another NULL, so NULLs never collideDDepends on the column type

Q3Key vocabularymedium

A minimal super key — one with no redundant columns — is called a…

Asked in

ACandidate keyBComposite keyCSurrogate keyDForeign key

Q4Referential integritymedium

With a plain foreign key (no ON DELETE clause), what happens when you delete a customer who still has orders?

Asked in

AThe delete is rejected with a constraint violationBThe customer and all their orders are deletedCThe orders' customer_id becomes NULLDThe delete succeeds and the orders become orphans

Q5ON DELETE optionshard

Order items have no meaning without their order — deleting an order should remove its items automatically. Which clause on the items' foreign key?

Asked in

AON DELETE CASCADEBON DELETE SET NULLCON DELETE RESTRICTDON DELETE IGNORE

Q6Self-referencing FKhard

employees.manager_id references employees.emp_id — a self-referencing foreign key. Why is ON DELETE CASCADE risky here?

Asked in

ASelf-referencing FKs can't have ON DELETE clausesBDeleting one manager cascade-deletes their entire reporting subtreeCIt would set every manager_id to NULLDIt creates an infinite loop and crashes

FAQ

Next lesson: why we split tables at all — Lesson 8: Normalization →