← SQL roadmap

Topic 16 of 52 · Beginner

Keys & Constraints

Primary keys, foreign keys, UNIQUE, NOT NULL, CHECK and DEFAULT.

Concept

Constraints are rules the database enforces so bad data can't get in. A PRIMARY KEY uniquely identifies each row and cannot be NULL.

A FOREIGN KEY makes a column refer to a primary key in another table, keeping the relationship valid. ON DELETE CASCADE removes child rows when the parent goes.

UNIQUE blocks duplicates, NOT NULL forces a value, CHECK tests a condition and DEFAULT fills a value when none is given.

Code (SQL · MySQL 8)

CREATE TABLE orders (
  id          INT AUTO_INCREMENT PRIMARY KEY,
  customer_id INT NOT NULL,
  amount      DECIMAL(10,2) CHECK (amount > 0),
  status      VARCHAR(20) DEFAULT 'pending',
  order_date  DATE NOT NULL,
  FOREIGN KEY (customer_id) REFERENCES customers(id)
    ON DELETE CASCADE
);

Remember

  • One primary key per table (it may span several columns).
  • UNIQUE allows NULLs; PRIMARY KEY does not.
  • Foreign keys protect referential integrity.

Interview questions

Primary key vs unique key?

Both prevent duplicates. A table has only one primary key and it cannot be NULL; it can have many unique keys and they allow NULL.

What is a composite key?

A primary key made of two or more columns, e.g. (student_id, course_id) in an enrolment table.

Practise SQL with the AI mentor

Lesson test

Take an 8-question test on “Keys & Constraints” — concepts, output prediction, bug spotting and interview questions.

Can't find a SQL topic you need for exams or placements? Request it and we'll add it.