Mysql - Composite Key
A MySQL composite key is a key that consists of two or more columns in a table, used together to uniquely identify a record (combination of values in the same table row). It can also be described as a primary key created on multiple columns.
When a composite key is created on multiple columns of a table, the combination of these columns guarantees uniqueness, even though individually these columns may or may not guarantee uniqueness. Therefore, when the database table doesn't have any column which is individually capable of identifying a unique row (or a record) from the table, then we might need two or more columns to get a unique record/row from the table.
- A composite key may or may not be a part of a foreign key constraint.
- A composite key cannot contain NULL values.
- A composite key can be created by combining more than one candidate key.
- It is also known as a compound key.
Creating MySQL Composite Key
To create a composite key in a MySQL table, you can specify a primary key on two or more columns of a table using the `PRIMARY KEY` keyword in the `CREATE TABLE` statement.
Following is the syntax to create a Composite Key while creating a table −
CREATE TABLE table_name(
column1 datatype, column2 datatype, column3 datatype...,
CONSTRAINT composite_key_name
PRIMARY KEY(column_name1, column_name2,..)
);
Dropping MySQL Composite Key
We can drop the MySQL Composite Key by using the `ALTER TABLE... DROP` statement. Following is the syntax to drop the Composite key from the column of a table −
ALTER TABLE table_name DROP PRIMARY KEY;
Terms of Use About Us Privacy Policy
All Rights Reserved.