# Composite Primary Keys in PostgreSQL

> In PostgreSQL, a composite primary key can be defined as a combination of two or more columns that guarantees the uniqueness of a record.

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.

---
[View this page online](https://www.commandprompt.com/education/composite-primary-keys-in-postgresql/)