PostgreSQL add primary key

In this comprehensive article, I’ll explain everything you need to know about adding primary keys in PostgreSQL, whether you’re creating new tables or modifying existing ones.

Postgresql add primary key

In Postgresql, the primary key is a field in a table that recognizes each row or record in a database table. It contains a unique value and doesn’t hold any null value.

Hence, we can additionally say that the primary key collects the unique and non-null constraints of a table. Any particular column with a primary key constraint can’t be null or empty.

It is used to recognize each record in a database table distinctively. A table can have other unique columns, but we have only one primary key in a database table, including single or multiple fields.

It is the most significant key when creating database tables, and it is a unique ID. It can be signified for one column or a group of columns.

Method 1: Creating a Table with a Primary Key

Now we will define the primary key while creating the table. Generally, the primary key is added to the table is when we explain the table’s structure using CREATE TABLE statement. Let’s check the query below.

Syntax

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

Example

The below statement creates a purchase order buyer_headers table with the name buyer_headers.

CREATE TABLE buyer_headers (
	buyer_no INTEGER PRIMARY KEY,
	distributor_no INTEGER,
	description TEXT,
	billing_address TEXT
);

The buyer_no is the primary key of the buyer_headers table, which uniquely identifies purchase orders in the buyer_headers table.

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

Method 2: Using the CONSTRAINT Keyword

Another approach is to use the CONSTRAINT Keywords to give your primary key a specific name.

CREATE TABLE Azureproducts (
    product_id INT NOT NULL,
    product_name VARCHAR(100) NOT NULL,
    price DECIMAL(10, 2) NOT NULL,
    CONSTRAINT pk_products PRIMARY KEY (product_id)
);

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

Method 3: Using the SERIAL Data type

We can use the query below for this purpose.

CREATE TABLE Newproducts (
    product_id SERIAL PRIMARY KEY,
    sku VARCHAR(50) UNIQUE NOT NULL
);

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

Method 4: Using BIGSERIAL Data type

We can execute the below query for this purpose.

CREATE TABLE Newemployees (
    employee_id BIGSERIAL PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    hire_date DATE NOT NULL
);

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

Creating Different Types of Primary Keys

PostgreSQL offers flexibility in how you implement primary keys. Let’s explore some common approaches:

Single-Column Primary Keys

This is the most common type, where one column uniquely identifies each row:

CREATE TABLE employeesNew (
    employee_id BIGSERIAL PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    hire_date DATE NOT NULL
);

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

Composite Primary Keys

Sometimes, a single column isn’t enough to uniquely identify rows. In such cases, you can create a primary key that spans multiple columns:

CREATE TABLE order_items (
    order_id INT NOT NULL,
    product_id INT NOT NULL,
    quantity INT NOT NULL,
    unit_price DECIMAL(10, 2) NOT NULL,
    PRIMARY KEY (order_id, product_id)
);

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

Check out PostgreSQL Add Primary Key Multiple Columns for more information.

UUID Primary Keys

We can create UUID Primary Keys using the below query.

CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

CREATE TABLE sessions (
    session_id UUID DEFAULT uuid_generate_v4() PRIMARY KEY,
    user_id INT NOT NULL,
    login_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    last_activity TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

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

Best Practices for PostgreSQL Primary Keys

Below are the best practices for working with primary keys.

Choose the Right Data Type

The choice of data type for your primary key impacts performance and storage:

Data Type	Advantages	Disadvantages
SERIAL/INT	Small, fast, simple	Limited range, reveals record count
BIGSERIAL/BIGINT	Larger range	Uses more storage
UUID	Globally unique, good for distributed systems	Larger storage, slower in indexes
Natural Keys	Meaningful to humans	May change, potentially complex

For most applications, I recommend using either:

• SERIAL/BIGSERIAL for single-database applications
• UUID for distributed systems or microservices

Conclusion

Following the methods and practices mentioned in this article, you can implement primary keys effectively in your PostgreSQL databases.

You may also like the following Postgres tutorials:

• Postgres date range
• Drop Database PSQL
• PostgreSQL Update
• Postgresql date comparison
• Postgresql date to string
• How to connect to PostgreSQL database
• Postgresql Having Clause