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
UNIQUEandNOT 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.