SQL - Composite Key

---

A Composite Key is a key that consists of two or more attributes (columns or fields) in a table. It can also be described as a Primary key created on multiple columns.Using the composite key, we can uniquely identify any record in a database table. A combination of these columns guarantees uniqueness, though the individual column may or may not guarantee for the uniqueness in a table.

A KEY is a set of one or more attributes whose combined values are unique among all occurrences in the given table.

Say we have a CUSTOMERS table with multiple columns from which we will select two columns named Adharcard_Id and Mobile_number and will combine them to make the Composite key, and that combination can be used to fetch any customers records uniquely in a table.

Let’s understand with a proper diagram −

Here are some important points about the Composite Key −

• A Composite Key may or may not be a part of the Foreign key.
• A Composite Key can not be NULL.
• A Composite Key also can be created by combining more than one Candidate Key.
• It is also known as Compound key.
• All the attributes in a compound keys are foreign keys.

Why do we need Composite Key?

When the database table doesn’t have any column which is alone capable of identifying a unique row (or a record) from the table, then we might need two or more two fields/columns to get a unique record/row from the table.

Syntax

Following is the syntax to create a COMPOSITE KEY while creating a table −

CREATE TABLE TABLE_NAME(COLUMN1, COLUMN2, COLUMN3…., CONSTRAINT COMPOSITE_KEY_NAME PRIMARY KEY(COLUMN1, COLUMN2,..);

Note − Here the COMPOSITE_KEY_NAME is the name of the Composite Key in a table. It is optional to specify this. It comes handy when you want to drop the composite key constraint from the column of the table.

Example

In the following example, we are trying to create a table named CUSTOMERS with multiple columns, and while creating the table will pass two columns named ADHARCARD_ID and MOBILE_NO to the PRIMARY KEY (ADHARCARD_ID, MOBILE_NO) to create a composite key on these columns.

Following is the statement to create table in SQL −

CREATE TABLE CUSTOMERS(
   ID INT NOT NULL,
   NAME VARCHAR (20) NOT NULL,
   AGE INT NOT NULL,
   ADHARCARD_ID BIGINT,
   MOBILE_NO BIGINT,
   ADDRESS CHAR (25) ,
   SALARY DECIMAL (18, 2),       
   CONSTRAINT CK_CUSTOMERS PRIMARY KEY (ADHARCARD_ID, MOBILE_NO)
);

Where the CK_CUSTOMERS is the name of a composite of this table.

Output

Following is the output of the above statement −

(0 rows affected)

Verification

As we have created a Composite Key for the customer's table by using the columns ADHARCARD_ID, and MOBILE_NO, the values of these columns can not be duplicated.

First of all, lets insert a record into a customers table −

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADHARCARD_ID,MOBILE_NO, ADDRESS,SALARY)
VALUES (1, 'Ramesh', 32, 901234984567, 9021345687,'Ahmedabad', 2000.00 );

Now, lets insert one more record with the same adharcard_id and mobile number into the customers table −

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADHARCARD_ID,MOBILE_NO,ADDRESS,SALARY)
VALUES (2, 'Khilan', 25,901234984567,9021345687, 'Delhi', 1500.00 );

You can observe that the second INSERT statement generates an error message saying “Violation of PRIMARY KEY” as shown below −

Msg 2627, Level 14, State 1, Line 14
Violation of PRIMARY KEY constraint 'CK_CUSTOMERS'. Cannot insert duplicate key in object 'dbo.CUSTOMERS'. The duplicate key value is (901234984567, 9021345687).

Dropping the Composite Key

If you have created the Composite key for the customer's table, then there must also be a way to drop the composite key. You can do that by using the ALTER TABLE… DROP statement.

Syntax

Following is the syntax to drop the Composite key from the column of a table −

ALTER TABLE TABLE_NAME DROP COMPOISTE_KEY_NAME; (in SQL)
ALTER TABLE TABLE_NAME DROP PRIMARY KEY;(in MYSQL)

Example

Using the following SQL statement, we can drop the Composite key constraint from the table −

ALTER TABLE CUSTOMERS DROP CK_CUSTOMERS;

Where CK_CUSTOMERS is the table composite key name.

Output

The above SQL statement produces the following output −

(0 rows affected)

Verification

Since, we have dropped the composite from the customer table, so now you can insert the duplicate value of the columns adharcard_id and mobile number.

Let’s insert two record with the same adharcard_id and mobile number into the Customers table −

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADHARCARD_ID,MOBILE_NO, ADDRESS,SALARY)
VALUES (1, 'Ramesh', 32, 901234984567, 9021345687,'Ahmedabad', 2000.00 );
INSERT INTO CUSTOMERS (ID,NAME,AGE,ADHARCARD_ID,MOBILE_NO,ADDRESS,SALARY)
VALUES (2, 'Khilan', 25,901234984567,9021345687, 'Delhi', 1500.00 );

If you verify the content of the table below, both the Customers have the same adharcard id and mobile number −

SELECT * FROM CUSTOMERS;

+----+--------+-----+--------------+------------+-----------+---------+
| ID | NAME   | AGE | ADHARCARD_ID | MOBILE_NO  | ADDRESS   | SALARY  |
+----+--------+-----+--------------+------------+-----------+---------+
|  1 | Ramesh |  32 | 901234984567 | 9021345687 | Ahmedabad | 2000.00 |
|  2 | Khilan |  25 | 901234984567 | 9021345687 | Delhi     | 1500.00 |
+----+--------+-----+--------------+------------+-----------+---------+