Composite Key MySQL

A composite key in MySQL is a combination of two or more columns in a table that allows us to identify each row of the specified table uniquely. It is a type of candidate key formed by more than one column.

Any keys such as primary, super, or candidate keys can be called composite keys when combined with more than one attribute. Helpful when we need to identify a table with more than one attribute uniquely. It can also have different data types.

Let us try to add a composite key using two ways:

Composite Primary Key in MySQL

CREATE TABLE toys (  
    Toy_ID int NOT NULL,   
    Name varchar(45),   
    Manufacturer varchar(45),  
    PRIMARY KEY(Name, Manufacturer)  
);  

• We have created table toys with two composite keys, Name and Manufacturer.

• We can see that when described, the table has two primary keys.
• Meaning it has successfully added the composite keys on name and manufacturer.

• We have added some data to toys; this is how we get the table.
• We cannot insert a duplicate value into this composite key column. Instead, any of the columns with a duplicate and the other with a different value is acceptable.

1. INSERT INTO Toy (Toy_ID, Name, Manufacturer)  
VALUES (001 'Monopoly', 'Funskool');  
  
2. INSERT INTO Toy (Toy_ID, Name, Manufacturer)  
VALUES (001 'Monopoly', 'Playman'); 

• No. 2 is acceptable, and one is not because it will throw an error saying it has a duplicate entry.

ALTER TABLE:

• We can modify the existing table with a composite key, and sometimes it is required to add composite keys to uniquely identify each record of the table with more than one attribute.

ALTER TABLE Crypto add primary key(cryp_id, coins);  

Conclusion:

So this is how composite keys are used and how they all make sense.