SQL Server Composite Key
A composite key in SQL Server is a combination of more than one column. It is used to identify the table rows uniquely. SQL Server guarantees the column’s uniqueness only when the columns are combined, and if they are taken separately, the uniqueness cannot be maintained. When a key, such as a primary key, super key, or candidate key, has more than one column, it is referred to as a composite key. We can use the different data types in a composite key column as there is no need to be the same data type for creating a composite key in SQL Server.
The syntax of the composite key is given below:
When does composite key make its appearance?
We don’t always have a unique column in a table that can be used as a primary key. In this case, we combine two or more table columns, make them unique, and use it as the table’s main key. This combination of columns or properties is referred to as a composite key.
SQL Server allows us to create a composite key in two ways:
- Using CREATE statement
- Using ALTER statement
Composite key using CREATE statement
SQL Server CREATE statement defines a table. It means we can define the composite key while creating a table. The below statement will create a table name Product:
We see that this statement creates a composite primary key with the Name and Manufacturer column. Now, we will insert some records into this table as below:
Verifying the table using SELECT statement display the below output:
Let us insert two more records and see the result.
Executing the above statement will return the below output:
We can see in the output that if we try to add a product name and a manufacturer that is the same, it will generate an error saying, “We cannot enter duplicate key in object dbo.Product.”
When we run the second insert command, it will be successfully added to the table. It’s because we can put as many mice as we like in the product column, but the manufacturer column must be unique.
Hence, we may conclude that the composite key always ensures the uniqueness of the table’s columns, which have two keys.
Composite key using ALTER statement
ALTER statement is always used to make changes to an existing table. To uniquely identify each record of the database with more than one attribute, the composite key is sometimes required. In that situation, an ALTER TABLE statement is used.
The syntax is given below:
Let us understand it with an example. First, we will create a table “student” using the below statement:
Now, we realize that the composite is required for this table. The below statement will add the composite key after the table creation:
The above statement will add the composite key ck_student in the table successfully.
How to delete the existing composite key?
SQL Server also allows us to delete the composite key from the table. We can do this by using the ALTER statement. The syntax is given below:
Suppose we want to remove the ck_product composite key from the Product table. We can write the query as below:
Executing the statement will remove the composite key ‘ck_product’ from the Product table.