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:

Composite Key in DBMS

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.






Latest Courses