Primary Keys, Foreign Keys and Constraints
Explain keys, uniqueness, and referential integrity the way an interviewer wants to hear it.
Primary Keys, Foreign Keys and Constraints is a free SQL Interview Prep lesson on CoddyKit — lesson 3 of 4. You can read the complete lesson below for free — then practise it hands-on in the browser with a built-in code editor and a 24/7 AI tutor. It is part of the SQL Interview Prep learning path, one of 4 lessons in the course, and your progress syncs across the web and the CoddyKit app.
Why Keys Come Up Every Time
After defining SQL, interviewers turn to keys and constraints. The theme tying them together is integrity: rules the database enforces so bad data can't get in.
What a Primary Key Is
A primary key uniquely identifies each row. It's always unique and NOT NULL, and a table has at most one. Say it cleanly to score points.
CREATE TABLE customers (
id INTEGER PRIMARY KEY,
email VARCHAR(255) NOT NULL,
name VARCHAR(100)
);Natural vs Surrogate Keys
Real data or a generated number? A natural key uses existing unique data; a surrogate key is a generated id. Most teams prefer surrogates: they're stable.
Composite Primary Keys
A primary key can span more than one column, a composite key. You see these in junction tables, where two foreign keys together identify each row.
CREATE TABLE enrollments (
student_id INTEGER,
course_id INTEGER,
enrolled_on DATE,
PRIMARY KEY (student_id, course_id)
);Primary Key vs Unique Constraint
A guaranteed question: primary key vs unique constraint. Both enforce uniqueness, but a table has one primary key (NOT NULL) and many unique constraints (each allows a NULL).
CREATE TABLE users (
id INTEGER PRIMARY KEY,
email VARCHAR(255) UNIQUE,
username VARCHAR(50) UNIQUE
);What a Foreign Key Is
A foreign key references another table's primary key, enforcing referential integrity: you can't add a child row pointing to a parent that doesn't exist.
CREATE TABLE orders (
id INTEGER PRIMARY KEY,
customer_id INTEGER REFERENCES customers(id),
amount NUMERIC(10,2)
);Referential Actions: ON DELETE / ON UPDATE
"What happens to orders if the customer is deleted?" That's a referential action: RESTRICT blocks it (the default), CASCADE deletes children, SET NULL clears the key.
CREATE TABLE order_items (
id INTEGER PRIMARY KEY,
order_id INTEGER REFERENCES orders(id) ON DELETE CASCADE
);NOT NULL, CHECK and DEFAULT
Three more constraints round out integrity: NOT NULL forbids missing values, CHECK enforces a rule like stock >= 0, and DEFAULT supplies a fallback value.
CREATE TABLE products (
id INTEGER PRIMARY KEY,
price NUMERIC(10,2) NOT NULL DEFAULT 0,
stock INTEGER CHECK (stock >= 0)
);Worked Example: Designing With Integrity
Let's design a library's loans table. The code below uses two foreign keys, a NOT NULL due date, and a unique rule so a book can't be loaned twice at once.
CREATE TABLE loans (
id INTEGER PRIMARY KEY,
book_id INTEGER NOT NULL REFERENCES books(id),
member_id INTEGER NOT NULL REFERENCES members(id),
due_date DATE NOT NULL,
returned BOOLEAN DEFAULT false
);How to Talk About Integrity
Name the three kinds of integrity to signal depth: entity from primary keys, referential from foreign keys, and domain from NOT NULL, CHECK, and types.
Adding Constraints to an Existing Table
Asked to add constraints to an existing table? Use ALTER TABLE ... ADD CONSTRAINT. A subtle point worth raising: it can fail if current rows already violate the new rule.
ALTER TABLE orders
ADD CONSTRAINT fk_orders_customer
FOREIGN KEY (customer_id) REFERENCES customers(id);Quick Check
Distinguish a primary key from a unique constraint.
Recap
Recap: a primary key is unique and NOT NULL, one per table; a unique constraint allows a NULL and you can have many; a foreign key enforces referential integrity.
Frequently asked questions
Is the “Primary Keys, Foreign Keys and Constraints” lesson free?
Yes — the full text of “Primary Keys, Foreign Keys and Constraints” is free to read here on the web, and the SQL Interview Prep course includes 4 lessons in total. To practise it interactively (a built-in code editor and a 24/7 AI tutor) and unlock the rest of the SQL Interview Prep course, upgrade to CoddyKit PRO.
What will I learn in “Primary Keys, Foreign Keys and Constraints”?
Explain keys, uniqueness, and referential integrity the way an interviewer wants to hear it. You practise SQL Interview Prep with hands-on code you run directly in the browser, and a 24/7 AI tutor answers your questions as you work through the lesson.
Do I need any experience to start SQL Interview Prep?
No prior experience is required. SQL Interview Prep on CoddyKit is structured for beginners through advanced learners; this is — lesson 3 of 4, so you can start here or from the beginning and move at your own pace.
How long does the “Primary Keys, Foreign Keys and Constraints” lesson take?
Most CoddyKit lessons take about 5–10 minutes. Each one is bite-sized and interactive, so you make steady progress and pick up exactly where you left off across the web and the app.
Can I write and run code in this SQL Interview Prep lesson?
Yes. Every SQL Interview Prep lesson includes a built-in code editor, so you write and run real code right in your browser and get instant AI feedback — no local setup required.
All lessons in this course
- What Is SQL and Why Interviewers Ask It
- Logical Query Execution Order
- Primary Keys, Foreign Keys and Constraints
- Reading a Schema Under Pressure