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 INTEGER PRIMARY KEY,
  name    VARCHAR(60) NOT NULL
);

CREATE TABLE Offices (
  office_id INTEGER PRIMARY KEY,
  city_id   INTEGER 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. SQLite refuses it with FOREIGN KEY constraint failed, which the playground rewords in plain English. 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.

That refusal needs one thing SQLite does not do by default. Plain SQLite ignores foreign keys until a connection runs PRAGMA foreign_keys = ON, and it has to be run again on every new connection. The playground runs it for you, which is why city 99 is refused here; in your own SQLite projects, run it yourself or the 99 goes in. PostgreSQL, MySQL (with InnoDB), SQL Server and Oracle enforce foreign keys without being asked.

The sample database on this site follows the same pattern: Orders.customer_id holds values from Customers.customer_id, and this join matches on them:

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;

The sample tables do not declare that FOREIGN KEY, though, so there the link is a habit the data keeps rather than a rule SQLite checks. Delete customer 101 in the playground and its three orders stay behind, pointing at a customer who no longer exists — exactly the orphan a declared foreign key would have refused to create.

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 — apart from one SQLite quirk covered under common misconceptions below.

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 INTEGER NOT NULL,
  course_id  INTEGER NOT NULL,
  grade      CHAR(1),
  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  INTEGER PRIMARY KEY,
  order_id INTEGER NOT NULL,
  FOREIGN KEY (order_id) REFERENCES Orders (order_id) ON DELETE CASCADE
);

INSERT INTO OrderLines VALUES (1, 5001), (2, 5001), (3, 5002);

DELETE FROM Orders WHERE order_id = 5001;

SELECT * FROM OrderLines;

Order 5001 had two lines. Deleting the order took both of them with it, and only the line for order 5002 is left. Take ON DELETE CASCADE off the foreign key, press Reset in the playground (it removes the OrderLines table and brings order 5001 back), and run the block again: the default takes over, and SQLite refuses the DELETE with FOREIGN KEY constraint failed, because two lines still point at order 5001. Both behaviours depend on the playground having switched foreign keys on; plain SQLite without PRAGMA foreign_keys = ON would delete the order and leave both lines pointing at nothing.

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. SQLite is the one exception you are likely to meet: to stay compatible with an old bug, in an ordinary table it accepts NULL in a primary key column that is not an INTEGER PRIMARY KEY unless that column is also declared NOT NULL — which is why the Enrolments example above spells it out.
• “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