Primary Key vs Foreign Key vs Unique Constraint

A primary key, a foreign key, and a unique constraint are three different SQL constraints that get confused with each other because they overlap in one place — all three can prevent duplicate values in a column — but they solve three distinct problems: identifying a row, linking it to another table, and enforcing uniqueness with no identity implied at all.

Primary keys: one per table

A primary key uniquely identifies each row in a table. Every table should have exactly one, and it’s what foreign keys in other tables point back to.

CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  email TEXT NOT NULL
);

A primary key is automatically NOT NULL and unique — the database enforces both without you specifying them separately. It’s also usually the column the database uses to physically organize the table’s storage, since most engines build a clustered or default index on the primary key automatically. That’s one reason primary key choice affects performance, not just correctness — see our guide to database indexing for how that index gets used at query time.

Primary keys can be a single column (a surrogate key like an auto-incrementing id) or span multiple columns (a composite key), which is common in join tables that represent a many-to-many relationship.

Foreign keys: linking tables together

A foreign key is a column (or set of columns) in one table that references the primary key of another table, enforcing that the referenced row actually exists.

CREATE TABLE orders (
  id SERIAL PRIMARY KEY,
  user_id INTEGER REFERENCES users(id),
  total NUMERIC
);

Here, orders.user_id can only hold values that exist as an id in users — insert an order with user_id = 999 when no such user exists, and the database rejects it. That’s the whole job of a foreign key constraint: it guarantees referential integrity, not uniqueness. Nothing stops the same user_id from appearing in many rows of orders — that’s the entire point, since one user can place many orders.

Foreign keys also govern what happens when the referenced row is deleted or updated, via ON DELETE and ON UPDATE clauses — CASCADE to delete dependent rows automatically, SET NULL to null out the reference, or RESTRICT to block the deletion outright until the dependent rows are handled.

Unique constraints: no identity required

A unique constraint just says “no two rows may share this value” — nothing more. It doesn’t identify the row (a table can have several unique constraints alongside its one primary key) and it implies no relationship to any other table.

CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  email TEXT UNIQUE NOT NULL
);

Here id is the primary key that other tables reference, while email is unique for a completely separate reason: the application needs to guarantee no two accounts share an address. Unlike a primary key, a unique constraint allows NULL in most databases (and typically allows more than one row with NULL, since NULL isn’t considered equal to another NULL), and a table can have as many unique constraints as it needs.

Comparing the three

	Primary key	Foreign key	Unique constraint
Purpose	Identifies each row	References another table’s primary key	Prevents duplicate values
Per table	Exactly one (can be composite)	Zero or more	Zero or more
Allows NULL	No	Yes (unless also NOT NULL)	Usually yes
Enforces uniqueness	Yes	No	Yes
Implies a relationship	No	Yes	No
Typically indexed	Always	Usually (for join performance)	Always

The overlap that causes confusion: a primary key is unique, and so is a unique constraint, but only a foreign key establishes a relationship between tables — a primary key on its own says nothing about any other table, and a unique constraint never does either.

Composite keys and real-world cases

Join tables that model many-to-many relationships often use a composite primary key made of two foreign keys:

CREATE TABLE enrollments (
  student_id INTEGER REFERENCES students(id),
  course_id INTEGER REFERENCES courses(id),
  PRIMARY KEY (student_id, course_id)
);

Here the primary key is the pair — a student can appear in many rows and a course can appear in many rows, but the same student can’t enroll in the same course twice, since that specific combination is what the primary key enforces uniqueness on. This pattern shows up constantly once you start writing SQL joins across normalized tables, and it’s a direct consequence of database normalization pushing many-to-many relationships into their own table rather than trying to represent them with repeated columns.

Constraint violations — inserting a duplicate primary key, referencing a foreign key that doesn’t exist, violating a unique constraint — are enforced at the database level regardless of what the application code does, which is part of what ACID transactions guarantee: a transaction that would violate a constraint is rolled back, not partially applied.

The takeaway

A primary key identifies a row and is what other tables reference. A foreign key is that reference — it enforces that a related row actually exists, but says nothing about uniqueness on its own. A unique constraint enforces no-duplicates without implying identity or a relationship to anything else. A table needs exactly one primary key, can have any number of foreign keys pointing out to other tables, and can have any number of unique constraints on top of that — three separate tools, each solving one piece of data integrity.