Composite Key in SQL

Composite Key in SQL

The Composite Key in SQL is a combination of two or more columns, which are used to identify the rows from a table. Here individually the specified columns will not be unique, the combination of the columns gets the uniqueness and able to fetch data from the table. A composite key is derived from a combination of two or more columns that combined make a unique column, which individually does not provide uniqueness. We can use all foreign keys in the composite keys. Data types specified in the composite key can be different.

When we use Composite Key?

In a table sometimes, we do not have any unique column, which could be defined as a primary key. In such a case, we combine two or more columns from a table, make it unique, and use it as the primary key in a table. This combined columns or attributes are called Composite Key.

Syntax:

Create table table_name (
Col1 data_type_1,
Col2 data_type_2,
Col3 data_type_3,
Col4 data_type_4,
….
CONSTRAINT COMP_NAME PRIMARY KEY (Col1, Col2)  ---------- Composite key declaration------------
);

How to use Composite Key in SQL?

Before going with the usage let go step by step by differentiating primary and composite key.

1. Primary Key Declaration

   SQL Syntax declaration for PRIMARY KEY Constraint defines as below:

Code:

Create table table_name (
Col1 data_type_1 NOT NULL,
Col2 data_type_2 NOT NULL,
Col3 data_type_3,
Col4 data_type_4,
….
PRIMARY KEY (Col1)                   ------------ Primary key declaration---------------------
);
(OR)
Create table table_name (
Col1 data_type_1 PRIMARY KEY,    ------------ Primary key declaration---------------------
Col2 data_type_2 NOT NULL,
Col3 data_type_3,
Col4 data_type_4,
….
);

2. Composite Key Declaration

   SQL Syntax declaration for COMPOSITE KEY Constraint defines as below: –

Code:

Create table table_name (
Col1 data_type_1 NOT NULL,
Col2 data_type_2 NOT NULL,
Col3 data_type_3,
Col4 data_type_4,
….
CONSTRAINT COMP_NAME PRIMARY KEY (Col1, Col2)     ------------ Composite key declaration-----------------
);

Example:

Create table Personal (
Person_ID INT NOT NULL,
Person_FNAME VARCHAR (25),
Person_LNAME VARCHAR (25),
Person_PHONE INT,
Person_ADDRESS VARCHAR (25),
CONSTRAINT COMP_KEY PRIMARY KEY (Person_ID, Person_PHONE)   ------------ Composite key declaration--------
);

Here in the above example, we are creating a table called “stud”, in which “S_FNAME” and “S_LNAME” combined becomes a composite key. A composite key will be unique and NOT NULL.