In RDBMS like PostgreSQL, the composite primary keys are used to uniquely identify a record. It is created by merging multiple column values that ensure uniqueness. More specifically, a composite primary key can be defined as the combination of multiple columns that ensures the uniqueness of a record. Selecting an individual column of a composite primary key doesn’t guarantee uniqueness.

This write-up explains the following aspects of the composite primary keys in PostgreSQL:

- What is the Need for Composite Primary Key in Postgres?
- How to Add or Define Composite Primary Keys in Already Existing Tables?
- How to Create/Define Composite Primary Keys While Creating a New Postgres Table?

What is the Need for Composite Primary Key in Postgres?

The need for a composite primary key arises when we have to identify the table’s records with two or more attributes uniquely. For instance, a composite primary key can be needed while maintaining the “product-order” details. Consider the following snippet for a profound understanding:

From the table, it can be observed that the uniqueness can’t be figured out based on a single column. Therefore, for better search purposes, the customer's details can be uniquely identified based on the “product_id” and “order_id” columns.

How to Add or Define Composite Primary Keys in Already Existing Tables?

Postgres allows us to add a composite primary key to an already existing table by utilizing the ALTER TABLE statement. To do that, the below-provided syntax is used in Postgres:

ALTER TABLE table_name
ADD PRIMARY KEY (col_list);

In this syntax:

- The “ALTER TABLE” statement is used to modify an already existing table.
- The “table_name” represents a table to be altered.
- The “PRIMARY KEY” constraint is used to define/create composite primary keys.
- The “col_list” represents the columns to be defined as the composite primary keys.

Example: Adding a Composite Primary Key

In this example, a composite primary key will be added to the “product_order” table by combining the “order_id” and “product_id” columns:

ALTER TABLE product_order 
ADD PRIMARY KEY (product_id, order_id);

In the above query, the “ALTER TABLE” command is executed with the “ADD PRIMARY KEY” clause to add a composite primary key in the “product_order” table:

The “ALTER TABLE” message in the output ensures that the given table has been modified. The table alteration can be confirmed using the following query:

SELECT * FROM product_order;

The output snippet proves that the composite primary key has been successfully added to an already existing table.

How to Create/Define Composite Primary Keys While Creating a New Postgres Table?

A composite primary key can be created/defined in Postgres while table creation. For this purpose, the below-provided syntax is used in Postgres:

CREATE TABLE table_name(
col_1 data_type, 
col_2 data_type, 
col_3 data_type,
…
col_n data_type,
PRIMARY KEY(col_list)
);

In this syntax:

- The “CREATE TABLE” statement is used to define a new Postgres table.
- The “table_name” represents a table to be created.
- col_1, col_2, …, col_n represent the column names.
- The “PRIMARY KEY” constraint is used to add/define the composite primary keys.
- The “col_list” represents the columns to be defined as the composite primary keys.

Example: Creating a Composite Primary Key

In the following example, a composite primary key will be created on the “product_id” and “order_id” columns:

CREATE TABLE customer_details(
product_id INTEGER,
order_id INTEGER,
price NUMERIC,
PRIMARY KEY (product_id, order_id)
);

The “CREATE TABLE” message in the output signifies that the desired table has been created. To verify the creation of the composite primary key, execute the “SELECT *” command:

SELECT * FROM customer_details;

The output shows that a composite primary key has been successfully created on two columns.

Conclusion

In PostgreSQL, the composite primary keys are used to uniquely identify a record. A composite primary key can be defined as a combination of two or more columns that guarantees the uniqueness of a record. The need for a composite primary key arises when we have to identify the table’s records with two or more attributes uniquely. This article presented a detailed guide on how to create a composite primary key while table creation or add a composite primary key by altering an already existing table.