Composite Key

A composite key is a combination of two or more columns in a table that can be used to uniquely identify each row in the table when the columns are combined uniqueness is guaranteed, but when it taken individually it does not guarantee uniqueness.

Sometimes more than one attributes are needed to uniquely identify an entity. A primary key that is made by the combination of more than one attribute is known as a composite key.

In other words we can say that:

Composite key is a key which is the combination of more than one field or column of a given table. It may be a candidate key or primary key.

Columns that make up the composite key can be of different data types.

SQL Syntax to specify composite key:

 CREATE TABLE TABLE_NAME  

(COLUMN_1, DATA_TYPE_1,  

COLUMN_2, DATA_TYPE_2,  

???  

PRIMARY KEY (COLUMN_1, COLUMN_2, ...)); 

In all cases composite key created consist of COLUMN1 and COLUMN2.

MySQL:

 CREATE TABLE SAMPLE_TABLE  

(COL1 integer,  

COL2 varchar(30),  

COL3 varchar(50),  

PRIMARY KEY (COL1, COL2)); 

MySQL:

  CREATE TABLE SAMPLE_TABLE  

(COL1 integer,  

COL2 varchar(30),  

COL3 varchar(50),  

PRIMARY KEY (COL1, COL2));  

Oracle:

CREATE TABLE SAMPLE_TABLE  

CREATE TABLE SAMPLE_TABLE  

(COL1 integer,  

COL2 varchar(30),  

COL3 varchar(50),  

PRIMARY KEY (COL1, COL2));  

SQL Server:

Let’s see the Syntax for the select top statement:

CREATE TABLE SAMPLE_TABLE  

(COL1 integer,  

COL2 nvarchar(30),  

COL3 nvarchar(50),  

PRIMARY KEY (COL1, COL2));