Composite Key In SQL

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.

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>