PostgreSQL - Primary Key

The PRIMARY KEY constraint in PostgreSQL uniquely identifies each record in a table. Every table can have only one primary key, which can consist of one or more columns.

• Prevent duplicate values in the primary key column.
• Disallow NULL values in the primary key.
• Improve query performance by automatically creating a unique index.

Syntax

PRIMARY KEY During Table Creation

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

PRIMARY KEY Using ALTER TABLE

ALTER TABLE table_name
ADD CONSTRAINT constraint_name
PRIMARY KEY (column_name);

Types of Primary Key

• Simple Primary Key: A primary key that consists of a single column.
• Composite Primary Key: A primary key that consists of two or more columns.

Examples

Firstly, create a 'customers' table and insert some records.

Example 1: Create a PRIMARY KEY During Table Creation

The following query creates a customers table with customer_id as the primary key.

Query:

CREATE TABLE customers (
    customer_id INT PRIMARY KEY,
    customer_name VARCHAR(50),
    city VARCHAR(50)
);

INSERT INTO customers VALUES
(1, 'James Carter', 'New York'),
(2, 'Emily Johnson', 'Chicago');

Output:

Example 2: Verify the PRIMARY KEY Constraint

The following queries attempt to insert duplicate and NULL values into the primary key column.

Query:

INSERT INTO customers
VALUES (1, 'Michael Brown', 'Dallas');

INSERT INTO customers
VALUES (NULL, 'Sophia Davis', 'Boston');

Output:

• The first query fails because customer_id = 1 already exists.
• The second query fails because the primary key column does not allow NULL values.

Example 3: Add a PRIMARY KEY to an Existing Table

Firstly, create a table without a primary key.

CREATE TABLE orders (
    order_id INT,
    customer_name VARCHAR(50),
    order_date DATE
);

Now, add a primary key to the order_id column.

Query:

ALTER TABLE orders
ADD CONSTRAINT PK_orders
PRIMARY KEY (order_id);

Output:

• The ALTER TABLE statement adds a primary key to the existing table.
• order_id must now contain unique and non-NULL values.

Query:

INSERT INTO orders
VALUES (1, 'Daniel Moore', '2026-07-20');

INSERT INTO orders
VALUES (1, 'Emma Wilson', '2026-07-21');

INSERT INTO orders
VALUES (NULL, 'Olivia Miller', '2026-07-22');

Output:

• The second insert fails because order_id = 1 already exists.
• The third insert fails because the primary key column cannot contain NULL values.

Example 4: Create a Table with a Composite PRIMARY KEY

The following query creates a student_courses table with a composite primary key consisting of student_id and course_id.

Query:

CREATE TABLE student_courses (
    student_id INT,
    course_id INT,
    enrollment_date DATE,
    PRIMARY KEY (student_id, course_id)
);

INSERT INTO student_courses VALUES
(101, 1, '2026-01-10'),
(101, 2, '2026-01-15'),
(102, 1, '2026-01-18'),
(103, 3, '2026-01-20');

Output: