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:

1. Using CREATE Statement

2. Using ALTER Statement

Let us understand it by creating a table "Product"

Now let us insert some values in the table:

Also like this to test the working of composite key

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.

Now that you completed mysql and DBMS Tutorials, lets have a quick recap of it

Share this page on :

FaceBook Whatsapp Twitter