PostgreSQL Primary Keys

In a relational database, a primary key is a column or combination of columns that uniquely identifies each row in a table.

PostgreSQL primary key rules

In PostgreSQL, primary keys need to follow these rules:

The primary key is defined on the table. A table is not mandatory to define a primary key, but can only define at most one primary key.
A primary key can contain one column or multiple columns.
Primary key column values must be unique. If the primary key contains multiple columns, the combine values of these columns must to be unique.
Primary key columns cannot contain null values.

A primary key is equivalent to combination of the UNION constraint and the NOT NULL constraint.

If the above rules are not followed, the following errors may be raised.

If more than one primary key is to be defined, the error will be returned: ERROR 1068 (42000): Multiple primary key defined.
If there are duplicate primary key values when inserting or updating, the error like ERROR 1062 (23000): Duplicate entry '1' for key 'users.PRIMARY' will be returned.
A similar errorERROR 1048 (23000): Column 'id' cannot be null will be returned if try to insert a null value.

PostgreSQL primary keys syntax

You can define the primary key while creating the table, as follows:

CREATE TABLE users (
  id INTEGER PRIMARY KEY,
  name VARCHAR(45)
);

This defines the id column as the primary key.

The above method is suitable for the case where only one column is used as the primary key. If the primary key contains multiple columns, please use the following method:

CREATE TABLE users (
  com_id INTEGER,
  user_number INTEGER,
  name VARCHAR(45),
  PRIMARY KEY(com_id, user_number)
);

This defines a primary key that contains 2 columns com_id and user_number.

Add a primary key

If you did not set the primary key when you created the table, and you want to add a primary key to it, use the following method:

ALTER TABLE users
ADD PRIMARY KEY(id);

Here, a primary key is added to the users table, which includes id the column.

Drop a primary key

If you want to drop the primary key on a table, perform the following steps:

Find the name of the primary key constraint using the \d command:

\d users

                     Table "public.users"
Column |         Type          | Collation | Nullable | Default
--------+-----------------------+-----------+----------+---------
id     | integer               |           | not null |
name   | character varying(45) |           |          |
Indexes:
   "users_pkey" PRIMARY KEY, btree (id)

You can find that the name of the primary key constraint is users_pkey in the last line.

Drop the primary key constraint with the following statement:
```
ALTER TABLE users
DROP CONSTRAINT users_pkey;
```

How to generate primary key value

In business systems, we do not use business columns as primary keys usually, although they are also unique. We generally use a separate column as the primary key, mainly for the following two reasons:

Protect business data
Easy to modify these business columns

In order to generate a unique primary key value, you usually use the following methods:

Set the primary key column to SERIAL.

PostgreSQL automatically generate consecutive integer values for those columns declared SERIAL. The following statement creates a SERIAL primary key.
```
CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  name VARCHAR(45)
);
```
Set the primary key column to UUID type.
```
CREATE TABLE users (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  name VARCHAR(45)
);
```
Here, the primary key column is of the UUID data type, and has a default value gen_random_uuid().

Primary key vs unique index

Both primary keys and unique indexes require values to be unique, but there are some differences between them:

Only one primary key can be defined in a table, but multiple unique indexes can be defined.
The value in the primary key cannot be NULL, while the value in the index can be NULL.

Conclusion

In this article, you learned what a primary key is, its rules, and how to use it in PostgreSQL. The main points of this article are as follows:

A table can only define at most one primary key.
A primary key can contain one column or multiple columns.
Primary key column values must be unique.
Primary key columns cannot contain null values.