Composite Key in DBMS
A ‘combination of two or more’ better describes the word ‘composite’. Thus, a composite key in DBMS is a candidate key that is composed of two or more attributes and is capable of uniquely identifying a table or a relation.
Here, in this section, we will understand and discuss about the composite key as well as its role. We will also look at its working by looking at an example.
What is a Composite Key
A composite key is the DBMS key having two or more attributes that together can uniquely identify a tuple in a table. Such a key is also known as Compound Key, where each attribute creating a key is a foreign key in its own right.
Use of Composite Key
Two or more attributes together form a composite key that can uniquely identify a tuple in a table. We need to find out such table columns combination that can form a candidate key and hence a composite key.
Example of Composite Key
Below is an example to understand the working of a composite key.
Consider table A having three columns or attributes, which are as follows:
Cust_Id: A customer id is provided to each customer who visits and is stored in this field.
Order_Id: Each order placed by the customer is given an order id, which is stored in this field.
Prod_code: It holds the code value for the products available.
Prod_name: An attribute holding the name of the product on the specified product code.
Below is the table that shows the diagram for the above table:
From the table, we found that no attribute is available that alone can identify a record in the table and can become a primary key. However, the combination of some attributes can form a key and can identify a tuple in the table. In the above example, Cust_Id and Prod_code can together form a primary key because they alone are not able to identify a tuple, but together they can do so.
Points to be noted:
- The attribute Cust_Id can work as a primary key alone if the other attributes which are identifiable through it are related to the customer’s details only, such as customer’s phone number, name, address, etc.
- Similarly, in the case of Prod_code, it fails to be a primary key alone for this table because it can identify the Prod_name but cannot identify the Cust_Id and Order_Id. Thus, we cannot make Prod_code as the primary key.
- Both Cust_Id and Prod_code together can identify all the records of the table because using such a combination, and we can identify the Order_Id of the customer and also can identify the Prod_name of the particular Prod_code.
- Therefore, {Cust_Id, Prod_code} is the composite key for the table.
Note: If any type of keys used in database management is formed via two or more attributes are known as Composite keys.