Definition of Composite key: A key that has more than one attributes is known as composite key. It is also known as compound key.
Note: Any key such as super key, primary key, candidate key etc. can be called composite key if it has more than one attributes.
Composite key Example
Lets consider a table Sales. This table has four columns (attributes) – cust_Id, order_Id, product_code & product_count.
Table – Sales
cust_Id order_Id product_code product_count -------- -------- ------------ ------------- C01 O001 P007 23 C02 O123 P007 19 C02 O123 P230 82 C01 O001 P890 42
None of these columns alone can play a role of key in this table.
Column cust_Id alone cannot become a key as a same customer can place multiple orders, thus the same customer can have multiple entires.
Column order_Id alone cannot be a primary key as a same order can contain the order of multiple products, thus same order_Id can be present multiple times.
Column product_code cannot be a primary key as more than one customers can place order for the same product.
Column product_count alone cannot be a primary key because two orders can be placed for the same product count.
Based on this, it is safe to assume that the key should be having more than one attributes:
Key in above table: {cust_id, product_code}
This is a composite key as it is made up of more than one attributes.
Anonymous says
“Key in above table: {cust_id, order_id}”. But this cannot be because 1st and 4th rows have the same key then and so does not uniquely identify the rows.. right?
Himanshu says
Agreed..
Any one of the both. cust_id or order_id should be different here i think, to make {cust_id, order_id} key.
pooja says
i think it should be {cust_id, product_code}
haritha says
what is the difference between composite key and candidate key?
Lee says
The difference is that candidate key does not allow redundant attributes only unique attributes like ID and Item Code etc.
Chaitanya Singh says
A composite key must have more than one attributes while a candidate key can contain a single attribute.