Primary key vs foreign key in a relational table
Verdict: A primary key uniquely identifies each row within its own table; a foreign key stores another table's primary key value to link rows, and the RDBMS enforces referential integrity so it must match an existing primary key.
| Criterion | Primary key | Foreign key |
|---|---|---|
| Purpose | Uniquely identifies a row in its own table | References a related table's primary key to link rows |
| Uniqueness | Must be unique within the table | May repeat; not required to be unique |
| Integrity enforcement | Is the identity every foreign key must match | RDBMS enforces referential integrity: value must match an existing primary key |
| Example | Customer.CustomerId identifies each customer row | Order.CustomerId references Customer.CustomerId |
Rules
- A relational database stores entities in tables, with each row uniquely identified by a primary key.
- Structured data adheres to a fixed schema, so every row shares the same columns.
- Normalizing a flat table means splitting each entity into its own table and linking them with foreign key columns that reference primary keys.
- A key can be a composite key based on a unique combination of multiple columns.
Traps
- A foreign key does not duplicate the referenced table's attributes into every row; it references the single stored record.
- Compressing repeated values on disk is a storage optimization handled by the engine, not normalization.
- Archiving old rows into a separate table on a schedule is a data-lifecycle practice, not normalization.