Composite Key in SQL

To know what a composite key is we need to have the knowledge of what a primary key is, a primary key is a column that has a unique and not null value in an SQL table. 

Now a composite key is also a primary key, but the difference is that it is made by the combination of more than one column to identify the particular row in the table.

Composite Key:

A composite key is made by the 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 of a row is guaranteed, but when it is taken individually it does not guarantee uniqueness, or it can also be understood as a primary key made by the combination of two or more attributes to uniquely identify every row in a table. 

Note: 

• A composite key can also be made by the combination of more than one candidate key.
• A composite key cannot be null.

Example:

Creating a database:

CREATE School;

Using database:

USE School;

Creating table with a composite key:

CREATE TABLE student
(rollNumber INT, 
name VARCHAR(30), 
class VARCHAR(30), 
section VARCHAR(1), 
mobile VARCHAR(10),
PRIMARY KEY (rollNumber, mobile));

In this example, we have made the composite key as the combination of two columns i.e. rollNumber and mobile because all the rows of the table student can be uniquely identified by this composite key.

Inserting records in the table:

INSERT INTO student (rollNumber, name, class, section, mobile) 
VALUES (1, "AMAN","FOURTH", "B", "9988774455");
INSERT INTO student (rollNumber, name, class, section, mobile) 
VALUES (2, "JOHN","FIRST", "A", "9988112233");
INSERT INTO student (rollNumber, name, class, section, mobile) 
VALUES (3, "TOM","FOURTH", "B", "9988777755");
INSERT INTO student (rollNumber, name, class, section, mobile) 
VALUES (4, "RICHARD","SECOND", "C", "9955663322");

Querying the records:

SELECT * FROM student;

OUTPUT: