A Composite Key in SQL is a key that is created by combining two or more columns in a table to uniquely identify a record. It is often used when no single column can uniquely identify rows in a table.
The following query creates a table named CUSTOMERS with a Composite Key on columns ID and NAME:
Here, ck_customers is the name of the Composite Key. It ensures no two rows can have the same combination of ID and NAME.
To drop a Composite Key, use the ALTER TABLE statement. For example:
Once dropped, duplicate values can be inserted into the combination of ID and NAME columns.
Let’s verify by inserting duplicate records into the CUSTOMERS table:
After dropping the Composite Key, the above query successfully inserts duplicate values into the ID and NAME columns.
To create a Composite Key on multiple columns, you can use the following SQL query. This query creates the CUSTOMERS table, and sets a Composite Key on the ID and NAME columns:
The query creates a Composite Key named ck_customers, ensuring no two records in the table can have the same combination of ID and NAME values.
Now, let’s insert some records into the CUSTOMERS table with the Composite Key. If we try to insert two rows with the same ID and NAME, it will result in a duplicate key error.
The second INSERT statement will fail because it violates the Composite Key constraint (the combination of ID and NAME must be unique).
To drop the Composite Key from the table, use the following SQL query:
After dropping the Composite Key, you can insert duplicate records into the CUSTOMERS table.
After dropping the Composite Key, you can insert duplicate values into the table. Below is an example of inserting duplicate ID and NAME values:
After executing this query, the table will allow duplicate ID and NAME combinations, as the Composite Key constraint has been removed.