Earlier lessons have used employees.department_id to connect employees to departments without really explaining why that works, or what stops it from pointing at a department that doesn't exist. Primary keys and foreign keys are the answer: the mechanism that makes relationships between tables actually reliable, not just a convention you have to remember to honor.
Learning Objectives
- Explain what a primary key guarantees, and what makes a column a good choice for one.
- Declare a foreign key that references another table's primary key.
- Choose the right ON DELETE behavior for a foreign key relationship.
Primary Key
Uniquely identifies a row
Foreign Key
References that primary key
Enforced
Database rejects invalid references
Reliable Relationship
No orphaned or duplicate data
What a Primary Key Guarantees
- Every value in a primary key column is unique, no two rows can share one.
- A primary key column can never be NULL, every row must have one.
- A table can have only one primary key, though it can span more than one column (a composite key).
- Most databases automatically create an index on the primary key, making lookups by it fast by default.
Natural Keys vs Surrogate Keys
The id columns used throughout this course (employees.id, departments.id) are surrogate keys. An email address looks tempting as a natural key, until a user needs to change their email and every row referencing it would need to change too.
- A real-world value that happens to be unique, like an email address or a national ID
- Meaningful on its own, no extra column needed
- Risky if that real-world value can ever change or turn out not to be unique after all
- An artificial value with no real-world meaning, almost always an auto-incrementing integer or a UUID
- Never needs to change, since it isn't tied to real-world data
- The default choice in most modern schema design
Declaring a Foreign Key
FOREIGN KEY (column) REFERENCES other_table(column) is standard SQL, and this exact syntax works unchanged across MySQL, PostgreSQL, SQL Server, and SQLite (SQLite requires foreign key enforcement to be turned on per connection with PRAGMA foreign_keys = ON, it's off by default for backward-compatibility reasons, a genuine SQLite-specific gotcha).
CREATE TABLE employees ( id INTEGER PRIMARY KEY, first_name VARCHAR(100), department_id INTEGER, FOREIGN KEY (department_id) REFERENCES departments(id) );
Once a foreign key exists, the database rejects any INSERT or UPDATE that would set department_id to a value that doesn't exist in departments.id. This is the mechanism, not just convention, that keeps employees.department_id trustworthy.
ON DELETE Behavior
This is the exact mechanism behind the foreign key error mentioned back in the DELETE lesson. Without an explicit ON DELETE clause, deleting a department with employees still assigned to it is blocked. ON DELETE SET NULL here means deleting a department instead unassigns its employees rather than blocking the delete or removing them.
| Option | What happens when the referenced row is deleted |
|---|---|
| RESTRICT (default in most databases) | The DELETE is blocked with an error, as long as a referencing row exists |
| CASCADE | Every referencing row is automatically deleted too |
| SET NULL | The foreign key column in every referencing row is set to NULL |
CREATE TABLE employees ( id INTEGER PRIMARY KEY, department_id INTEGER, FOREIGN KEY (department_id) REFERENCES departments(id) ON DELETE SET NULL );
Composite Keys
Sometimes no single column is unique on its own, but a combination of columns is. A table tracking which employees worked on which projects might use PRIMARY KEY (employee_id, project_id), unique per combination, even though neither column is unique alone. This is standard SQL, supported identically across all four databases.
Frequently Asked Questions
Can a table have more than one foreign key?
Yes, a table can reference as many other tables as it needs to. An orders table might have a foreign key to customers and another to employees (the salesperson), both enforced independently.
Does a foreign key column have to be named the same as the column it references?
No, department_id can reference departments.id just fine, the names don't need to match, only the data type and the values do.
Why is SQLite's foreign key enforcement off by default?
For backward compatibility: foreign key support was added to SQLite well after it was already widely deployed, and turning it on by default would have broken existing databases that relied on the old, unenforced behavior. New SQLite projects should explicitly run PRAGMA foreign_keys = ON.
Common Beginner Mistakes
Choosing a natural key that later turns out not to be unique, or needs to change
Email addresses get typos corrected, phone numbers get reassigned, usernames get renamed. A surrogate key (an auto-incrementing id) avoids ever needing to touch every row that references it.
Forgetting to enable foreign key enforcement in SQLite
Without PRAGMA foreign_keys = ON, SQLite will silently accept a department_id that doesn't exist in departments, no error, no warning. This is the single most common SQLite-specific gotcha for developers coming from MySQL or PostgreSQL, where enforcement is always on.
Using ON DELETE CASCADE without thinking through what it actually deletes
CASCADE can ripple further than expected, deleting a customer with CASCADE set on orders, and CASCADE again on order_items, silently removes an entire history. RESTRICT (the safer default) or SET NULL are usually better starting points than CASCADE.
Best Practices
- Default to a surrogate key (auto-incrementing integer or UUID) as the primary key, reserve natural keys for data that's genuinely guaranteed stable and unique.
- Always declare the foreign key relationship explicitly in the schema, don't rely on application code alone to keep references valid.
- Choose ON DELETE behavior deliberately for every foreign key, don't leave it at the default without considering what should actually happen.
- In SQLite, turn on PRAGMA foreign_keys = ON for every new connection, it isn't on by default.
Interview Questions
What's the difference between a primary key and a unique constraint?
Both enforce uniqueness, but a table can only have one primary key (which also can't be NULL), while it can have multiple unique constraints on different columns, and a unique constraint's column can be NULL (with NULL typically not conflicting with other NULLs). A primary key is also what foreign keys in other tables reference.
Write a foreign key that automatically deletes an employee's project assignments when the employee is deleted.
FOREIGN KEY (employee_id) REFERENCES employees(id) ON DELETE CASCADE on the project_assignments table, deleting the employee row automatically removes every project_assignments row pointing at them.
Why would you choose SET NULL over CASCADE for an orders table's foreign key to customers?
CASCADE would silently delete an entire order history when a customer record is removed, losing business records that might be needed for accounting or compliance. SET NULL keeps the orders but marks them as no longer tied to an active customer, preserving the history while still reflecting that the customer is gone.
Summary
This lesson covered what a primary key guarantees, natural keys versus surrogate keys, declaring a foreign key with FOREIGN KEY ... REFERENCES, and the three common ON DELETE behaviors, RESTRICT, CASCADE, and SET NULL, that control what happens to related rows.
What's Next?
The next lesson covers CREATE TABLE in full, including the other constraints available beyond primary and foreign keys: NOT NULL, UNIQUE, CHECK, and DEFAULT.
Ready to Practice SQL?
Write and run real SQL queries online, right in your browser, no install or signup required. Pick a playground based on which database you want to practice on.
Both open a third-party SQL playground in a new tab. External site terms and privacy policy apply.