Introduction to PostgreSQL primary key

A primary key is a column or a group of columns used to uniquely identify a row in a table. The column that participates in the primary key is known as the primary key column.

A table can have zero or one primary key. It cannot have more than one primary key.

It is a good practice to add a primary key to every table. When you add a primary key to a table, PostgreSQL creates a unique B-tree index on the column or a group of columns used to define the primary key.

Technically, a primary key constraint is the combination of anot-null constraint anda UNIQUE constraint.

Typically, you define primary for a table when creating it:

CREATE TABLE table_name (  column_1 data_type PRIMARY KEY,  column_2 data_type,  …);

In this syntax, you define the primary key as the column constraint of the primary key column.

If the primary key consists of more than one column, you can define it using the table constraint:

CREATE TABLE table_name (  column_1 data_type,  column_2 data_type,  column_3 data_type,  …  PRIMARY KEY(column_1, column2, ...));

To add a primary key to an existing table, you theALTER TABLE ... ADD PRIMARY KEY statement:

ALTER TABLE table_nameADD PRIMARY KEY (column_1,column_2, ...);

If you don’t explicitly specify the name for the primary key constraint, PostgreSQL will assign a default name to the primary key constraint.

By default, PostgreSQL uses the formattable-name_pkey as the default name for the primary key constraint.

To assign a name for the primary key, you can use theCONSTRAINT clause as follows:

CONSTRAINT constraint_namePRIMARY KEY(column_1, column_2,...);

PostgreSQL primary key examples

Let’s explore some examples of using the PostgreSQL primary key.

1) Creating a table with a primary key that consists of one column

The following statement creates a table with a primary key that consists of one column:

CREATE TABLE orders(  order_id SERIAL PRIMARY KEY,  customer_id VARCHAR(255)NOT NULL,  order_date DATE NOT NULL);

In this example, we create the orders with the order_id as the primary key.

We define the order_id column with the type SERIAL so that PostgreSQL will generate a unique integer (1, 2, 3, and so on) when you insert a new row into the table without providing the value for the order_id column.

This ensures the value in the order_id is unique for every row in the table.

2) Creating a table with a primary key that consists of two columns

The following example shows how to define a primary key that consists of two columnsorder_id anditem_no:

CREATE TABLE order_items(  order_idINT,  item_noSERIAL,  item_descriptionVARCHAR NOT NULL,  quantityINTEGER NOT NULL,  priceDEC(10,2),  PRIMARY KEY (order_id, item_no));

3) Adding a primary key to an existing table

First, create a table calledproducts without defining any primary key.

CREATE TABLE products (  product_idINT,  name VARCHAR(255)NOT NULL,  description TEXT,  priceDEC(10,2)NOT NULL);

Second, add a primary key constraint to theproducts table using theALTER TABLE ... ADD PRIMARY KEY statement:

ALTER TABLE productsADD PRIMARY KEY (product_id);

4) Adding an auto-incremented primary key to an existing table

First, create a new table calledvendors that does not have a primary key:

CREATE TABLE vendors (  name VARCHAR(255));

Second, insert some rows into thevendors table:

INSERT INTO vendors (name)VALUES  ('Microsoft'),  ('IBM'),  ('Apple'),  ('Samsung')RETURNING*;

Output:

name----------- Microsoft IBM Apple Samsung(4 rows)

Third, add a primary key namedvendor_id into thevendors table with the typeSERIAL:

ALTER TABLE vendorsADD COLUMN vendor_idSERIAL PRIMARY KEY;

Finally, verify the vendor_id column:

SELECT  vendor_id,  nameFROM  vendors;

Output:

vendor_id |   name-----------+-----------         1 | Microsoft         2 | IBM         3 | Apple         4 | Samsung(4 rows)

Drop a primary key

To remove a primary key from a table, you use the followingALTER TABLE statement:

ALTER TABLE table_nameDROP CONSTRAINT primary_key_constraint;

In this syntax:

• First, specify the name of the table from which you want to remove the primary key.
• Second, provide the primary key constraint to drop.

Let’s take an example of removing the primary key constraint from thevendors table using psql.

First, display the structure of thevendors table using the \d command:

\d vendors

Output:

Table "public.vendors"  Column   |          Type          | Collation | Nullable |                  Default-----------+------------------------+-----------+----------+-------------------------------------------- name      | character varying(255)|           |          | vendor_id | integer                |           | not null | nextval('vendors_vendor_id_seq'::regclass)Indexes:    "vendors_pkey" PRIMARY KEY, btree (vendor_id)

The output indicates that the primary key constraint is vendors_pkey.

Second, drop the primary key from thevendors table using theALTER TABLE ... DROP CONSTRAINT statement:

ALTER TABLE vendorsDROP CONSTRAINT vendors_pkey;

Output:

ALTER TABLE

The statement removes only the primary key constraint but does not remove the vendor_id column:

SELECT vendor_id, nameFROM vendors;

Output:

vendor_id |   name-----------+-----------         1 | Microsoft         2 | IBM         3 | Apple         4 | Samsung(4 rows)

Summary

• Use thePRIMARY KEY constraint to define a primary key for a table when creating the table.
• Use theALTER TABLE ... ADD PRIMARY KEY statement to add a primary key to a table.
• Use theALTER TABLE ... DROP CONSTRAINT statement to drop a primary key from a table.

Last updated onJanuary 25, 2024

On this page

• Introduction to PostgreSQL primary key
• PostgreSQL primary key examples
• Drop a primary key
• Summary