Primary Key vs Foreign Key in SQL
A primary key uniquely identifies each row in its own table; a foreign key references a primary key in another table to establish a relationship.
Published September 19, 2026
A primary key uniquely identifies each row in its own table; a foreign key is a column that points to a primary key in another table, linking the two. Together they enforce relationships.
The concept
A primary key must be unique and not NULL, and a table has only one. It is the row's identity.
A foreign key must match an existing value in the referenced table (or be NULL). The database rejects rows that point to something missing, which stops orphaned data.
Orders point to customers
Practical examples
Define both
CREATE TABLE customers (
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(100)
);
CREATE TABLE orders (
id INT PRIMARY KEY AUTO_INCREMENT,
customer_id INT NOT NULL,
FOREIGN KEY (customer_id) REFERENCES customers(id)
ON DELETE CASCADE
);Rejected insert
INSERT INTO orders (customer_id) VALUES (999);
-- ERROR: foreign key constraint failsComposite primary key
PRIMARY KEY (order_id, product_id)Common mistakes
- Using mutable data such as email as a primary key.
- Forgetting an index on the foreign key column.
- Mismatched types between FK and PK columns.
- Using CASCADE delete without realising it removes child rows.
Key takeaways
- PK = identity; FK = link to another table's PK.
- FKs prevent orphan rows.
- Choose ON DELETE behaviour deliberately.
FAQ
Can a table have multiple foreign keys?
Yes, as many as it has relationships.
Can a foreign key be NULL?
Yes unless the column is NOT NULL, which means 'no related row'.