Primary Key vs Foreign Key in SQL: The Difference Explained

A primary key identifies a row inside its own table. A foreign key points at a row in another table. That is the whole difference, and every other distinction — how many you can have, whether NULL is allowed, what happens on delete — follows from it.

The two work as a pair: the foreign key is only meaningful because the primary key it points at is guaranteed unique. This page shows the pair in action, then covers the design decisions that come up in interviews and in real schemas.

The difference in one table

The row worth dwelling on is the index one, because it is the most common real-world performance mistake. A primary key gets an index for free. A foreign key generally does not, and a join or a cascading delete on an unindexed foreign key is slow for exactly that reason. See how indexes work.

The pair in action

Two tables: cities, and offices that must be in a city that exists:

CREATE TABLE Cities (
  city_id INT PRIMARY KEY,
  name    STRING NOT NULL
);

CREATE TABLE Offices (
  office_id INT PRIMARY KEY,
  city_id   INT NOT NULL,
  FOREIGN KEY (city_id) REFERENCES Cities (city_id)
);

INSERT INTO Cities  VALUES (1, 'Pune');
INSERT INTO Offices VALUES (10, 1);

SELECT o.office_id, c.name AS city
FROM Offices o
JOIN Cities c ON c.city_id = o.city_id;

Now try inserting an office in city 99. The engine refuses it: Foreign key "99" not found in table "Cities". Without that FOREIGN KEY line the column would accept 99 happily, and you would have an office in a city that does not exist — a row no join will ever return, which is how orphaned data quietly accumulates.

The sample database on this site is built the same way. Orders.customer_id points at Customers.customer_id, which is what makes this join possible at all:

SELECT o.order_id, c.company, o.order_total
FROM Orders o
JOIN Customers c ON c.customer_id = o.customer_id
ORDER BY o.order_total DESC
LIMIT 5;

Choosing a primary key

The single most consequential schema decision, and the one interviewers probe with “could you use the email as the primary key?”. You can. You usually should not.

• It has to be unique — forever. Names are not. Phone numbers get reassigned. Even “national id + date of birth” has collisions in real datasets.
• It should never change. This is the big one. Change a primary key and every foreign key pointing at it has to change in the same transaction. People change their email addresses; that is a data update, not a schema migration.
• It should be small. Every index on the table and every foreign key elsewhere stores a copy of it. A 200-character text key is copied everywhere.
• It must never be NULL. That is enforced, not advice.

Which is why most schemas use a surrogate key — an integer or UUID that means nothing outside the database — and put a UNIQUE constraint on the email instead. You get both guarantees, and the identity is immune to the real world changing its mind. A natural key (a real attribute like a country code) is defensible when the value genuinely never changes and is genuinely unique, which is rarer than it sounds.

Composite keys

A primary key can be several columns together, which is the natural fit for a table that records a relationship between two things:

CREATE TABLE Enrolments (
  student_id INT,
  course_id  INT,
  grade      STRING,
  PRIMARY KEY (student_id, course_id)
);

INSERT INTO Enrolments VALUES (1, 100, 'A'), (1, 101, 'B');

SELECT * FROM Enrolments;

Student 1 can be on two courses, and a course can have many students, but the same student cannot be enrolled on the same course twice — the combination is what must be unique. Neither column alone is a key; the pair is.

A foreign key can be composite too, and then it must match the parent key column for column, in order. Composite keys are correct here and awkward everywhere else: every child table has to carry both columns, so many teams add a surrogate enrolment_id and keep a UNIQUE (student_id, course_id) constraint alongside it. Both designs are defensible; be able to say why you chose yours.

What happens when the parent row is deleted?

This is the part of foreign keys that changes behaviour rather than just refusing things. The referential action decides what the database does to the children:

CREATE TABLE OrderLines (
  line_id  INT PRIMARY KEY,
  order_id INT NOT NULL,
  FOREIGN KEY (order_id) REFERENCES Orders (order_id) ON DELETE CASCADE
);

ON UPDATE takes the same options and matters far less, precisely because a well-chosen primary key never changes.

Common misconceptions

• “A foreign key must reference a primary key.” It must reference a column with a unique guarantee, which can be a UNIQUE constraint rather than the primary key. In practice it is nearly always the primary key.
• “A foreign key must be unique.” The opposite: it is normally repeated. A hundred orders for one customer means that customer id appears a hundred times.
• “A table can have several primary keys.” One, which may be made of several columns. Several unique constraints, yes; several primary keys, no.
• “A primary key can be NULL if it is composite.” No column of a primary key can be NULL.
• “Foreign keys slow everything down, so leave them out.” They cost a check per write and buy a guarantee that no amount of application code can provide. Index the column and the cost is small; the data you save is not.

Practice this topic

Reading explains the idea; producing it yourself is what makes it stick. These exercises use exactly what this page covers, and your answer is checked by running it against the same sample database:

• Put a name to the departmentMedium
• Every department, even the quiet onesHard
• Name the managerHard
• Customers with a shipped orderMedium

See all 42 exercises →

Frequently asked questions

Related reading

• SQL constraints: NOT NULL, UNIQUE, CHECK and keys
• Database normalization: 1NF, 2NF and 3NF
• SQL JOINs explained: how keys become results
• SQL indexes: why a foreign key needs one
• 25 SQL interview questions and answers