PostgreSQL: Primary Key Constraint

In PostgreSQL, a primary key constraint uniquely identifies rows. One or more columns can be defined as a primary key in a table.

Primary key is a combination of NOT NULL constraint and UNIQUE constraint, meaning, the column or group of columns that are defined as the primary key enforce column to have not-null and unique values.

A table can have only one primary key. It is not mandatory but a good practice to add a primary key to a table.

Define Primary Key while Creating a Table

In PostgreSQL, you can define a primary key on a single column by writing "primary key" after the column name in the CREATE TABLE statement.

For example, the following CREATE TABLE statement will create the employee table with a primary key defined on emp_id column. This is called defining a primary key at the column level.

Example: Define Primary Key

CREATE TABLE IF NOT EXISTS employee
(emp_id INT PRIMARY KEY,
first_name VARCHAR(50),
last_name VARCHAR(50),
gender CHAR(1),
birthdate DATE,
email VARCHAR (100),
salary INT);

PostgreSQL will create a primary key constraint as table_name_pkey that's employee_pkey for the employee table, as shown below.

To define the primary key on multiple columns, it needs to be defined at the table level like below.

Syntax: Multi-column Primary Key

CREATE TABLE [IF NOT EXISTS] table_name (
   Column1 data_type(length),
   Column2 data_type(length),
    ...
CONSTRAINT  PRIMARY KEY(Column1, Column2,...)
);

        

For example, the following defines a primary key on two columns:

Example: Define Multi-column Primary Key

CREATE TABLE IF NOT EXISTS employee_department 
(emp_id  INT,
dept_id INT,
CONSTRAINT empid_deptid_pkey PRIMARY KEY (emp_id, dept_id));

Configure PRIMARY KEY Constraint on Existing Tables

A primary key constraint can be added to existing tables too, provided the column or group of columns being added to the primary key do not have NULL values in it and have unique values across the table.

Syntax: ALTER TABLE

ALTER TABLE table_name
ADD PRIMARY KEY(column1, column2,...);

---OR

ALTER TABLE table_name
ADD CONSTRAINT constraint_name
PRIMARY KEY(column1, column2,...);

Consider we created employee table without any primary key and inserted data as bellow

Now let's add the primary key on emp_id column. As emp_id does not have NULL values in any of the columns and all values are unique, you can alter the table and define emp_id as a primary key, as shown below.

Example: Define Primary Key in Existing Table

ALTER TABLE employee
ADD PRIMARY KEY(emp_id);

Define Primary Keys using pgAdmin

You can create primary keys on new or existing tables using pgAdmin. The steps would remain the same if you define primary keys on new tables or existing tables.

Expand your databases node in the left pane and then expand Schemas -> public -> Tables nodes.

Now, right-click on the table where you want to add a primary key and click on 'Properties' in the menu.

In the popup, go to the 'Constraints' tab and click on the 'Primary Key' tab, as shown below.

Now, to add a primary key, click on the + button to add a row in a grid. Then, click on the edit icon to add a primary column definition. In the General tab, add primary key name and in the Definition tag, add primary key columns, as shown below.

Click on the Save button to define a primary key on the selected columns.

Thus, you can define a primary key in a new or existing tables.