SQL, Unique and Primary keys

With a table created with this command:

CREATE TABLE people (
  age INT NOT NULL,
  name CHAR(20) NOT NULL
);

We can insert an item more than once.

And in particular, we can have columns that repeat the same value.

Nothing stops you from adding two people named ‘Flavio’, or the same person twice by mistake. The table has no rule against it, so the database happily accepts the duplicate. If your application logic assumes names are unique, you now have a data problem that no amount of application code fixes reliably.

The right place for that rule is the schema itself.

The UNIQUE constraint

We can force a column to have only unique values using the UNIQUE key constraint:

CREATE TABLE people (
  age INT NOT NULL,
  name CHAR(20) NOT NULL UNIQUE
);

Now if you try to add the ‘Flavio’ twice:

INSERT INTO people VALUES (37, 'Flavio');
INSERT INTO people VALUES (20, 'Flavio');

You’d get an error:

ERROR:  duplicate key value violates unique constraint "people_name_key"
DETAIL:  Key (name)=(Flavio) already exists.

The first insert succeeded. The second one was rejected and wrote nothing. That’s the behavior you want: the database guards the rule for every client, every time, even when a bug or a manual query tries to break it.

Primary keys

A primary key is a unique key that has another property: it’s the primary way we identify a row in the table.

CREATE TABLE people (
  age INT NOT NULL,
  name CHAR(20) NOT NULL PRIMARY KEY
);

A table can have many UNIQUE columns, but only one primary key. The primary key also refuses NULL values, because a row identifier that might be missing is useless.

The primary key can be an email in a list of users, for example.

The primary key can be a unique id that we assign to each record automatically.

Whatever that value is, we know we can use it to reference a row in the table.

My advice: prefer the auto-assigned id over a natural value like an email. Emails change. When a user renames their address, every reference to that row would need updating. A meaningless numeric id never changes, so references to it stay valid forever.

Unique and primary keys are backed by indexes under the hood. If you’re wondering which other columns in your queries deserve an index, I built a free index advisor that suggests the right CREATE INDEX statement.