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));

Tags: Tags: , ,