Composite key, or composite primary key, refers to cases where more than one column is used to specify the primary key of a table. In such cases, all foreign keys will also need to include all the columns in the composite key. Note that the columns that make up a composite key can be of different data types.
Below is the SQL syntax for specifying a composite key:
CREATE TABLE TABLE_NAME
(COLUMN_1 DATA_TYPE_1,
COLUMN_2 DATA_TYPE_2,
…
PRIMARY KEY (COLUMN_1, COLUMN_2, …));
Some database-specific examples are shown below. In all cases the composite key created consists of COL1 and COL2.
MySQL
CREATE TABLE SAMPLE_TABLE
(COL1 integer,
COL2 varchar(30),
COL3 varchar(50),
PRIMARY KEY (COL1, COL2));
Oracle
CREATE TABLE SAMPLE_TABLE
(COL1 integer,
COL2 varchar(30),
COL3 varchar(50),
PRIMARY KEY (COL1, COL2));
SQL Server
CREATE TABLE SAMPLE_TABLE
(COL1 integer,
COL2 nvarchar(30),
COL3 nvarchar(50),
PRIMARY KEY (COL1, COL2));
Composite Key Example
An example is when we want to record the history of student class registration and grade history. For this, we want to record which student took what class during what quarter, along with the date of registration, date of drop (if the class was dropped), and the final grade. Our table will look like the following:
Table Class_History
Quarter_ID |
Student_ID |
Course_ID |
Date_Registered |
Date_Dropped |
Final_Grade |
In this table, the first three fields, Quarter_ID, Student_ID, Course_ID, together form a composite key. Each of them individually cannot uniquely identify each record, but together the combination of all three does uniquely identify each record.