PostgreSQL – Composite Primary Key

In Relational databases, a primary key is a column(or multiple columns) that is used to uniquely identify rows of a table. A table can have only one primary key.

In most scenarios, an id column is created to serve as a primary key column.

Example:

CREATE TABLE employee
(employee_id smallint,
first_name varchar(50),
last_name varchar(50),
PRIMARY KEY pk_employee(employee_id)
);

In scenarios where one column cannot uniquely identify a record, primary key can be created on multiple columns. A primary key spanning over multiple columns is termed as a ‘composite primary key’.

Example:

CREATE TABLE employee_department
(employee_id smallint,
department_id smallint,
PRIMARY KEY pk_emp_dept(employee_id, department_id);

Consider an employee_department table which associates employees with departments. An employee can be part of multiple departments. In this scenario, combination of employee_id and department_id has to be unique. You can define a composite primary key as shown above.

Share this:

Related