PostgreSQL Add Primary Key Multiple Columns

In this article, I’ll explain everything you need to know about implementing multi-column primary keys in PostgreSQL. You’ll also learn the various methods and best practices for effectively implementing composite primary keys in PostgreSQL.

PostgreSQL Add Primary Key Multiple Columns

Let us discuss all the methods to achieve this.

Method 1: Creating a New Table with a Multi-Column Primary Key

The simple approach is to define a composite primary key during table creation. To this end, you can execute the query below.

Example

In this example, I’ve created a table for the relationship between students and courses. The combined values of student_id and course_id must be unique across the table.

CREATE TABLE student_courses (
    student_id INTEGER,
    course_id INTEGER,
    enrollment_date DATE NOT NULL,
    grade CHAR(2),
    PRIMARY KEY (student_id, course_id)
);

After executing the above query, I got the expected output, as shown in the below screenshot.

Method 2: Adding a Multi-Column Primary Key to an Existing Table

If you already have a table but need to add a composite primary key, there are two approaches depending on your specific situation.

Using ALTER TABLE with ADD CONSTRAINT

This is the most common approach when your table already has data that complies with primary key requirements. The below query adds a primary key constraint named pk_order_items on the combination of order_id and product_id columns.

ALTER TABLE order_items
ADD CONSTRAINT pk_order_items
PRIMARY KEY (order_id, product_id);

After executing the above query, I got the expected output, as shown in the screenshot below.

If you get any errors, you can try following the steps below.

1. First, remove all the duplicates using the query below.

DELETE FROM order_items
WHERE ctid NOT IN (
    SELECT min(ctid)
    FROM order_items
    GROUP BY order_id, product_id
);

2. Now, ensure no nulls are present in the key columns using the query below.

UPDATE order_items SET order_id = 0 WHERE order_id IS NULL;
UPDATE order_items SET product_id = 0 WHERE product_id IS NULL;

3. Finally, add the primary key using the below query.

ALTER TABLE order_items
ADD CONSTRAINT pk_order_items
PRIMARY KEY (order_id, product_id);

Method 3: Using Table Inheritance with Different Primary Keys

In more complex scenarios, we can use PostgreSQL’s table inheritance option.

1. The first step is to create parent table with basic structure using the below query.

CREATE TABLE transactions (
    transaction_date DATE NOT NULL,
    amount DECIMAL(10,2) NOT NULL
);

After executing the above query, I got the expected output as shown in the below screenshot.

2. Now let us create the child table for online sales with composite PK using the below query.

CREATE TABLE online_transactions (
    online_order_id VARCHAR(20),
    payment_method VARCHAR(50) NOT NULL,
    PRIMARY KEY (transaction_date, online_order_id)
) INHERITS (transactions);

I got the expected output after executing the above query.

3. Now let us create the child table for in-store sales with different composite PK using the below query.

This approach allows flexible primary key structures while maintaining a common parent structure.

Best Practices

Below are the lists of best practices, you should follow while working with multiple columns primary keys.

Column Order Matters

When defining a multi-column primary key, place the most selective columns first as mentioned below. This improves index performance:

PRIMARY KEY (company_id, year, quarter, transaction_id)

Follow Naming Conventions

Always use clear, descriptive constraint name that helps with maintenance and troubleshooting as mentioned below.

ADD CONSTRAINT pk_orders_products PRIMARY KEY (order_id, product_id);

Real-World Examples

Let’s explore some practical applications I’ve implemented for clients in various industries:

Example: Event Logging System

You can use the below query to create an event logging system.

CREATE TABLE system_events (
    event_date DATE NOT NULL,
    event_time TIME NOT NULL,
    server_id VARCHAR(50) NOT NULL,
    event_sequence INTEGER NOT NULL,
    event_type VARCHAR(100) NOT NULL,
    event_details TEXT,
    PRIMARY KEY (event_date, server_id, event_time, event_sequence)
);

The table created successfully after executing the above query as shown in the below screenshot.

Conclusion

Implementing multi-column primary keys in PostgreSQL provides a great way to work with primary keys for your database. Throughout this article, I’ve shared three methods for adding composite primary keys to your PostgreSQL tables, along with best practices with real-world examples.

You may also like following the articles below.

• PostgreSQL add primary key
• PostgreSQL Add Primary Key Autoincrement
• PostgreSQL Add Primary Key To Existing Column