Primary Key vs Unique Key

Both guarantee that no two rows carry the same value in the column set, but they differ on three points: how many you may declare, what NULL is allowed to do, and what the key means to the engine.

CREATE TABLE accounts (
  id INTEGER PRIMARY KEY,        -- identifies the row; exactly one per table
  email TEXT NOT NULL UNIQUE,    -- a business rule; many of these are allowed
  external_ref TEXT UNIQUE       -- a business rule that tolerates NULLs
);

INSERT INTO accounts (id, email, external_ref) VALUES
  (1, '[email protected]', 'EXT-100'),
  (2, '[email protected]', NULL);

SELECT id, email, external_ref FROM accounts ORDER BY id;

Count. A table has at most one primary key. It can have any number of unique constraints — most real schemas carry three or four, one per business identifier that must not repeat.

NULL. This is the part people get wrong. A UNIQUE constraint permits multiple NULLs, because in SQL NULL means "unknown", and two unknowns are not known to be equal. The primary key column is NOT NULL by definition, so exactly one null key is not an option. Notice the second row above: external_ref is NULL and no conflict arises, while email could never repeat.

Meaning. The primary key is the row's identity — the value other tables reference in foreign keys, the value that shows up in a WHERE id = ? lookup, and the value a replica routes on. A unique constraint is a data-quality rule about one attribute: this email has not been used before.

Mechanically the two are close. In SQLite both create a unique index, in PostgreSQL both create a unique B-tree index, and in MySQL the primary key is additionally the clustered index so the whole row is physically ordered by it — which is why a MySQL table with an AUTO_INCREMENT primary key and a random UUID unique key has very different write behaviour.

Several UNIQUE constraints, yes — as many as the business needs. Several primary keys, no. A table has exactly one primary key, and it may be made up of several columns together, which is a different thing entirely.

The distinction matters because people say "composite key" and mean one of two structures. A composite unique constraint is one constraint spanning several columns: it forbids the whole tuple from repeating while allowing each column to repeat on its own. A composite primary key is the table's single primary key, built from several columns, and it identifies rows jointly rather than individually.

Consider a booking table where a room can be booked by many guests but the same room cannot be booked twice on the same night. Neither room_id nor night is unique alone; the pair is. That is a composite unique constraint. If instead the pair were the only way to identify a booking — no separate booking_id — then the pair is the composite primary key.

Two practical consequences follow. First, the rule that a primary key column must be NOT NULL applies to every column in a composite primary key, whereas in a composite unique constraint some columns may be NULL. Second, indexes differ: a composite unique constraint creates one index over the column list, and lookups only use it efficiently when the query filters on the same leftmost columns.

For an interview answer, say it plainly: one primary key per table, unlimited unique constraints, and a key may span multiple columns when no single column identifies the row.

The engine raises a constraint violation rather than silently overwriting: SQLite and PostgreSQL both report UNIQUE constraint failed: accounts.email, MySQL reports error 1062, SQL Server reports 2601 or 2627. The transaction is left in a failed state in PostgreSQL and must be rolled back; in SQLite the statement is simply rejected. Either way, the row is not written, and the application must decide what that means.

CREATE TABLE accounts (
  id INTEGER PRIMARY KEY,
  email TEXT NOT NULL UNIQUE
);

INSERT INTO accounts (id, email) VALUES (1, '[email protected]');

-- A plain re-insert would raise: UNIQUE constraint failed: accounts.email
-- Make the write idempotent instead of fatal:
INSERT INTO accounts (id, email) VALUES (2, '[email protected]')
ON CONFLICT (email) DO NOTHING;

-- Or treat the collision as an update of the row that already exists:
INSERT INTO accounts (id, email) VALUES (2, '[email protected]')
ON CONFLICT (email) DO UPDATE SET email = excluded.email;

SELECT id, email FROM accounts ORDER BY id;

The final SELECT shows two rows: id 1 with [email protected] and id 2 with [email protected]. The second insert hit the conflict and did nothing; the third hit it and applied the update.

Choosing between the two ON CONFLICT actions is the real design decision. DO NOTHING suits idempotent ingestion — retrying the same message must not create a second row. DO UPDATE suits a last-writer-wins upsert, where the incoming row is newer and should replace the stored one; referencing excluded.col gets the value that was proposed for insertion rather than the stored value.

MySQL spells the same thing INSERT ... ON DUPLICATE KEY UPDATE, and SQL Server uses MERGE for it. Two cautions worth raising: DO NOTHING silently swallows genuine collisions, so log the conflict count; and catching the error and retrying in a loop is usually the wrong shape, because two concurrent requests can both fail the check before either commits.