PRIMARY KEY Constraint

The PRIMARY KEY constraint identifies each row in a table. A primary key column must be unique and not NULL.

A table can have only one primary key. It can be one column, or a combination of columns (a composite key).


Syntax

CREATE TABLE table_name (
  column_name datatype PRIMARY KEY
);

For a composite primary key:

CREATE TABLE table_name (
  col1 datatype,
  col2 datatype,
  PRIMARY KEY (col1, col2)
);

Or for a table that already exists:

ALTER TABLE table_name
ADD PRIMARY KEY (column_name);

Example

Our employees table already has a primary key. It is the id column:

CREATE TABLE employees (
  id INT PRIMARY KEY,
  ...
);

So you cannot add a second employee with an id that is already used:

INSERT INTO employees
VALUES (2, 'Ben', 29, 'Finance', 'male');

Output:

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

The insert failed, because id = 2 already belongs to Bob.

A primary key can't be NULL either:

INSERT INTO employees (name, age)
VALUES ('Ben', 29);

Output:

ERROR:  null value in column "id" of relation "employees" violates not-null constraint
DETAIL:  Failing row contains (null, Ben, 29, null, null).

Key Points

  • A primary key is always UNIQUE and NOT NULL.
  • A table can have only one primary key.
  • Other tables often point to it with a FOREIGN KEY.

Next, you will learn FOREIGN KEY, which links two tables together.

Answer the question below to choose a good primary key.