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.
