An identity column gives each row a number. Nothing stops a second row with the same number: an INSERT can give it by hand, or a BY DEFAULT counter can give a number that is already used. Then "customer 3" can mean two rows.

PRIMARY KEY

CREATE TABLE badges (
  id   integer PRIMARY KEY,
  name text NOT NULL
);

A primary keyprimary keyThe column (or columns) whose value identifies each row: unique and never NULL is the column that identifies each row. PRIMARY KEY makes two rules:

• unique: no two rows have the same value
• not null: every row has a value

The table tabs show a key icon next to a primary key column.

The database refuses a duplicate

ERROR: duplicate key value violates unique constraint "badges_pkey"
DETAIL: Key (id)=(2) already exists.

• badges_pkey is the name of the constraint. PostgreSQL names a primary key <table>_pkey.
• DETAIL gives the column and the value that is already used.

A NULL key fails with the not-null error from Chapter 4.

Rules

• A table has at most one primary key. A second PRIMARY KEY gives multiple primary keys for table "badges" are not allowed.
• The key does not change. Other tables use it to point to the row (Lesson 5).
• The usual key is a number from the database: id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY.
• A key can also be a value from the real world, such as the course code CS-340 in course. Use one only when it is unique and never changes.