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.
Lesson test
Take an 8-question test on “Keys & Constraints” — concepts, output prediction, bug spotting and interview questions.