Every table built since Module 7 already has a PRIMARY KEY, id on departments, employees, and projects. This lesson covers it properly, and finally writes the composite primary key project_assignments has needed since it was first created.
Learning Objectives
- Define PRIMARY KEY precisely, and explain why it's really NOT NULL and UNIQUE combined.
- Write a composite primary key spanning more than one column.
- Distinguish a surrogate key from a natural key, and explain when each is the better choice.
What PRIMARY KEY Actually Is
A PRIMARY KEY uniquely identifies every row in a table, no two rows can share the same primary key value, and it can never be NULL. It isn't a third, separate rule, it's NOT NULL and UNIQUE applied together to whichever column (or columns) represent the row's real identity, with one extra restriction neither of those two has alone: a table can only have one PRIMARY KEY, while it can have many separate UNIQUE constraints.
project_assignments' Composite Primary Key
project_assignments never had a single id column, its real identity is the pairing of employee_id and project_id together, exactly the composite constraints overview and the unique-constraint lesson both forward-referenced. A composite PRIMARY KEY can only be written table-level, there's no single column to attach PRIMARY KEY to on its own. This also makes the earlier uq_assignment UNIQUE constraint from the previous lesson redundant, a composite PRIMARY KEY already guarantees that exact pairing can't repeat, most real schemas would keep only one of the two.
ALTER TABLE project_assignments ADD CONSTRAINT pk_project_assignments PRIMARY KEY (employee_id, project_id);
Adding or Dropping a PRIMARY KEY on an Existing Table
| Database | Drop an Existing Primary Key |
|---|---|
| MySQL / MariaDB | ALTER TABLE t DROP PRIMARY KEY; |
| PostgreSQL / SQL Server / Oracle | ALTER TABLE t DROP CONSTRAINT pk_name; |
| SQLite | Not supported directly, recreate the table instead |
MySQL and MariaDB are the odd ones out here, DROP PRIMARY KEY needs no name since a table only ever has one. Every other database drops it like any other named constraint, with DROP CONSTRAINT and the name chosen when it was created.
Natural Key vs Surrogate Key
| Surrogate Key | Natural Key | |
|---|---|---|
| What it is | A meaningless, database-generated id, like employees.id | A real-world value that's already unique, like email |
| Beacon Robotics example | employees.id, auto-incrementing, covered in full next lesson | email could have served as a natural key instead |
| Advantage | Never changes, even if real-world details (email, name) change | No extra column needed, the data is already the identity |
| Disadvantage | An extra column that carries no business meaning on its own | Breaks if the real-world value ever needs to change or isn't guaranteed unique |
Beacon Robotics uses surrogate keys (auto-incrementing id) throughout, the far more common real-world default, specifically because an employee's email or name could legitimately change, while id never does. A natural key is occasionally the right call when a truly stable, already-unique real-world value exists, like a country's ISO code.
Try It Yourself
Write a composite PRIMARY KEY named pk_order_items on a table order_items(order_id, product_id), on MySQL.
ALTER TABLE order_items ADD CONSTRAINT pk_order_items PRIMARY KEY (order_id, product_id);
Frequently Asked Questions
Can a PRIMARY KEY span more than two columns?
Yes, there's no fixed limit on how many columns a composite primary key can include, though two or three is by far the most common in practice. project_assignments only needs two, employee_id and project_id, to uniquely identify each assignment.
Does changing a PRIMARY KEY value cascade to foreign keys referencing it?
Only if an ON UPDATE action is explicitly declared on the foreign key, covered in the next lesson. Without one, updating a referenced primary key value that other rows still point to is typically blocked entirely by the foreign key constraint.
Common Beginner Mistakes
Assuming a table needs a single-column id even when a composite key fits better
A junction table like project_assignments doesn't need a meaningless extra id column, the natural composite of employee_id and project_id already uniquely identifies each row. Adding an unnecessary surrogate id on top is extra complexity without benefit here.
Declaring two separate PRIMARY KEY constraints on one table
This is rejected outright, a table can only have one PRIMARY KEY, full stop. A second unique-identifying rule needs a UNIQUE constraint instead, not a second PRIMARY KEY.
Best Practices
- Default to a surrogate auto-incrementing id for most tables, it's stable even when real-world details change.
- Use a composite primary key for junction/linking tables instead of bolting on an unnecessary surrogate id.
- Name primary key constraints explicitly (pk_ prefix) on every table-level declaration.
Interview Questions
Explain the difference between a surrogate key and a natural key, with a real example.
A surrogate key is a database-generated value with no real-world meaning, like employees.id, it exists only to identify the row and never changes. A natural key is a real-world value that happens to already be unique, like an email address, used directly as the primary key. Surrogate keys are generally preferred because real-world values can change (a person can change their email) or turn out not to be as unique as assumed, breaking the key.
Write the composite PRIMARY KEY for project_assignments(employee_id, project_id), on PostgreSQL, and explain why it can't be column-level.
ALTER TABLE project_assignments ADD CONSTRAINT pk_project_assignments PRIMARY KEY (employee_id, project_id); — column-level syntax attaches a constraint to exactly one column by writing it inline with that column's own definition, there's no single column here to attach it to since the identity is the pair, so it has to be declared table-level, as its own separate clause naming both columns.
Summary
PRIMARY KEY uniquely identifies every row, combining NOT NULL and UNIQUE, with exactly one allowed per table. It can be a single surrogate column (employees.id) or a composite of multiple columns (project_assignments' employee_id + project_id), and the choice between a surrogate and a natural key comes down to stability, a surrogate key never needs to change even when the real-world data behind a row does.
What's Next?
FOREIGN KEY is next, the constraint that actually enforces every relationship this course's schema diagram has been drawing since Module 4.
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.