MySQL Composite key 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.
Any key such as primary key, super key, or candidate key and any other 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.
A composite key can be added in two ways and they are:
- by Using CREATE Statement
- by Using ALTER Statement
Let us see both ways in detail.
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) );
Next, execute the below command to show the table data:
SELECT * FROM Product;
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:
INSERT INTO Product (Prod_ID, Name, Manufacturer) VALUES (101, 'Soap', 'Hamam'), (102, 'Shampoo', 'Teresme'), (103, 'Oil', 'Daber Almond');
Now, execute the ALTER TABLE statement to add a composite primary key as follows:
ALTER TABLE Student add primary key(stud_id, subject);
We can verify the composite primary key added into a table or not using the following command: