Summary -

In this topic, we described about the Composite Key with detailed example.

The primary key with two or more fields is known as composite key.

Composite key, or composite primary key, specifies to the scenarios where more than one column is used to retrieve the unique information from the table. In such cases, all foreign keys need to include all the columns of the composite key.

To fetch data more uniquely we can add multiple columns on one primary key in table.


Note :- The columns that make up a composite key can be of different data types.

Syntax for creating Composite key while creating table – 

CREATE TABLE table_name ( 
       column1,
       column2,
       column3, 
       [CONSTRAINT primarykey_name] PRIMARYKEY (requiredcolumn1,
        requiredcolumn2, …));
  • required_column_name1, required_column_name2 - Represents the name of the columns which are part of primary key.

Syntax for creating Composite key on existing table -

ALTER table table_name [CONSTRAINT primarykey_name] 
PRIMARY KEY (required_column_name1, required_column_name2…..);
  • required_column_name1, required_column_name2 - Represents the name of the columns which are part of primary key.

Syntax for deleting Composite key on existing table -

ALTER TABLE table_name 
 DROP CONSTRAINT existing_primary_name;
  • existing_primary_name – Represents the name of the existing composite key which we want to delete.
  • table_name – represents the name of the table.

Examples -

Let us consider below table(s) as an example table(s) to frame the SQL query for getting the desired results.

employee_details -

emp_id emp_name designation manager_id date_of_hire salary dept_id
001 Employee1 Director 2019-07-11 45000.00 1000
002 Employee2 Director 2019-07-11 40000.00 2000
003 Employee3 Manager Employee1 2019-07-11 27000.00 1000
004 Employee4 Manager Employee2 2019-10-08 25000.00 2000
005 Employee5 Analyst Employee3 2019-07-11 20000.00 1000
006 Employee6 Analyst Employee3 2019-10-08 18000.00 1000
007 Employee7 Clerk Employee3 2019-07-11 15000.00 1000
008 Employee8 Salesman Employee4 2019--09-09 14000.00 2000
009 Employee9 Salesman Employee4 2019-10-08 13000.00 2000

Scenario – Creating Composite key while creating a table.

Requirement – Creating employee as Composite key while creating employee_details table with emp_id, dept_id. The query was as follows - 

CREATE TABLE employee_details (
     emp_id INT NOT NULL,
     dept_id INT NOT NULL,
     emp_name VARCHAR (20),
     CONSTRAINT ck_employee PRIMARY KEY (emp_id, dept_id));

By executing above query, ck_employee was created as composite key on employee_details table.


Note :- If we try to include specific columns to create composite key that columns should be declared as NOT NULL.

Scenario – Creating composite key on existing table.

Requirement – Creating employee as composite key on existing employee_details table which does not have a composite key column. The query was as follows - 

ALTER TABLE employee_details ADD CONSTRAINT ck_employee
 PRIMARY KEY (emp_id, dept_id);

By executing above query, we can create composite key on existing employee_details table.


Note - Before creating composite key, we should know that the columns which we try to include in the key do not have the duplicate values. i.e. the combination of two columns data should be unique.

Scenario – Deleting existing composite key.

Requirement – Deleting existing ck_employee composite key on employee_details table. The query was as follows - 

ALTER TABLE employee_details DROP CONSTRAINT ck_employee;

By executing above query, we can delete existing composite key on employee_details table.