MySQL Composite Key

A composite key in MySQL is a combination of two or more than two columns in a table that allows us to identify each row of the table uniquely. It is a type of candidate key which is formed by more than one column. MySQL guaranteed the uniqueness of the column only when they are combined. If they have taken individually, the uniqueness cannot maintain.

Any key such as primary key, super key, or candidate key can be called composite key when they have combined with more than one attribute. A composite key is useful when the table needs to identify each record with more than one attribute uniquely. A column used in the composite key can have different data types. Thus, it is not required to be the same data type for the columns to make a composite key in MySQL.

A composite key can be added in two ways:

Using CREATE Statement
Using ALTER Statement

Let us see both ways in detail.

Composite Key Using CREATE Statement

Here, we are going to understand how composite key works in MySQL. Let us first create a table "Product", using the following statement:

CREATE TABLE Product (
	Prod_ID int NOT NULL, 
	Name varchar(45), 
	Manufacturer varchar(45),
	PRIMARY KEY(Name, Manufacturer)
);

In the above statement, we have created a composite primary with the column names Name and Manufacturer.

We can verify the same using the command as below:

After the successful execution, we can see that the Key column has two PRI. It means we have successfully added the composite primary key on Name and Manufacturer columns.

Next, we need to insert the values into this table as given below:

INSERT INTO Product (Prod_ID, Name, Manufacturer)
VALUES (101, 'Soap', 'Hamam'),
(102, 'Shampoo', 'Teresme'),
(103, 'Oil', 'Daber Almond');

Next, execute the below command to show the table data:

It will give the output below:

Again execute the below insert statement to understand composite key more clearly:

INSERT INTO Product (Prod_ID, Name, Manufacturer)
VALUES (101, 'Soap', 'Hamam');

INSERT INTO Product (Prod_ID, Name, Manufacturer)
VALUES (101, 'Soap', 'LUX');

In the below output, we can see that if we try to add the combination of the same product name and manufacturer, then it will throw an error saying that: Duplicate entry for product.primary.

If we execute the second insert statement, it will be added successfully into the table. It is because we can insert any number of soap in the product column, but the manufacturer column should be different.

Hence, we can say that the composite key always enforces the uniqueness of the columns of that table, which has two keys.

Composite Key Using ALTER TABLE Statement

ALTER statement always used to do the modification into the existing table. Sometimes it is required to add the composite key to uniquely identify each record of the table with more than one attribute. In that case, we use an ALTER TABLE statement.

Let us first create a table "Student" using the below statement:

CREATE TABLE Student(
  stud_id int NOT NULL,
  stud_code varchar(15),
  stud_name varchar(35),
  subject varchar(25),
  marks int
);

Now, execute the ALTER TABLE statement to add a composite primary key as follows:

We can verify the composite primary key added into a table or not using the following command:

In the output, we can see that the key column has PRI, which means we have successfully added the composite primary key to stud_id and subject columns.