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

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.

Learn Constraints (PRIMARY KEY, FOREIGN KEY, UNIQUE, CHECK) in our free SQL course — a beginner-friendly interactive lesson with runnable queries, a practice…

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.

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

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.

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 .

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.

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.

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

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.

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.

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

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.

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

Practice quiz

How many PRIMARY KEY constraints can a single table have?

  • As many as you like
  • At most two
  • Exactly one
  • None — it is optional and forbidden

Answer: Exactly one. A table can have exactly one PRIMARY KEY (it may span multiple columns as a composite key).

A PRIMARY KEY is automatically equivalent to which combination?

  • UNIQUE + NOT NULL
  • CHECK + DEFAULT
  • FOREIGN KEY + UNIQUE
  • NOT NULL only

Answer: UNIQUE + NOT NULL. PRIMARY KEY = UNIQUE + NOT NULL, giving each row a never-blank, never-duplicated identity.

How does a UNIQUE constraint differ from a PRIMARY KEY?

  • UNIQUE forbids NULLs more strictly than PRIMARY KEY
  • UNIQUE can only be used on numeric columns
  • There is no difference at all
  • UNIQUE usually allows NULLs and you can have many per table

Answer: UNIQUE usually allows NULLs and you can have many per table. UNIQUE typically permits NULLs and a table can have several UNIQUE columns, unlike the single PRIMARY KEY.

What does a CHECK constraint do?

  • Links a column to another table
  • Enforces a custom condition on every INSERT and UPDATE
  • Automatically fills in a missing value
  • Speeds up queries on the column

Answer: Enforces a custom condition on every INSERT and UPDATE. CHECK enforces a custom rule such as price >= 0, rejecting rows that violate it.

What does a FOREIGN KEY guarantee?

  • A value must match an existing key in the referenced table
  • A value is never NULL
  • A value is unique across the table
  • A column auto-increments

Answer: A value must match an existing key in the referenced table. A FOREIGN KEY enforces referential integrity: the value must exist in the parent table — no orphan rows.

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

  • The customer is created automatically
  • The order inserts with a NULL customer
  • The FOREIGN KEY rejects the insert
  • Nothing — foreign keys are not checked on insert

Answer: The FOREIGN KEY rejects the insert. Referential integrity blocks an order that points at a non-existent customer.

What does ON DELETE CASCADE do when a parent row is deleted?

  • Blocks the delete while children exist
  • Automatically deletes the child rows too
  • Sets the child foreign keys to NULL
  • Throws an error every time

Answer: Automatically deletes the child rows too. CASCADE removes the children along with the parent. RESTRICT blocks it; SET NULL nulls the FK.

Which constraint stops a rating of 7 in a 1-to-5 scale?

  • UNIQUE (rating)
  • NOT NULL
  • PRIMARY KEY
  • CHECK (rating BETWEEN 1 AND 5)

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

What is the benefit of naming a constraint with CONSTRAINT name?

  • It makes inserts faster
  • Readable error messages and the ability to drop it by name later
  • It allows duplicate values
  • It disables the constraint

Answer: Readable error messages and the ability to drop it by name later. Named constraints report a clear name on violation and can be dropped with ALTER TABLE ... DROP CONSTRAINT name.

What does the NOT NULL constraint forbid?

  • Storing the number zero
  • Storing duplicate values
  • Leaving the column with no value (empty)
  • Storing text values

Answer: Leaving the column with no value (empty). NOT NULL requires a value to always be present; it is unrelated to zero or duplicates.