CODEWITHSUNDEEP
postgresql
Rahul Gour
Rahul Gour

Reputation: 527

Regarding single column or composite primary key

Is it better to have a single column primary key, or use composite primary keys . I have examples below:

 create table emp(
 subsidiary_id  smallint   , 
 Employee_id    int        ,
 emp_name       varchar,
 constraint emp_pkey primary key ( subsidiary_id   , Employee_id     )

Data will be line 

 subsidiary_id , employee_id
    1,                 1
    1,                 2
    1,                 3
    2,                 4 
    3,                 5
    2,                 6

employee id cannot be same for any subsidiary , always unique but on my reports using both the column in where clause as subsdiary_id = 1 and employee_id = 1 or and so on

so want to know to better approach to create primary on employee_id column or both which one is better?

Upvotes: 0

Views: 44

Answers (1)

Laurenz Albe
Laurenz Albe

Reputation: 246338

It sounds like a composite primary key is the right solution for you:

CREATE TABLE emp (
   subsidiary_id  smallint  NOT NULL, 
   employee_id    int       NOT NULL,
   emp_name       text,
   CONSTRAINT emp_pkey PRIMARY KEY (subsidiary_id, employee_id)
);

The index that is created for this constraint will also be useful for queries where only subsidiary_id appears in the WHERE condition.

Upvotes: 1

Related Questions