Difference between Candidate & Composite Key
Candidate key
It is a key on any single column or multiple columns in a table so as to maintain the row as unique. There can be multiple candidate keys on a single table. If there are multiple candidate keys in a single table, then one will be primary (without NULL value) and rest all will be alternate keys (can have single NULL value).
Candidate keys are those that follow below three rules
1) Unique Value in Table: This rule says that the column which is being considered as candidate key should have unique values.
2) Not Null: The values in the candidate key can’t contain NULL value.
3) Static: The value should be static in the candidate key column so that it should not be changed and will be available as same in all its references or calls.
Example of Candidate Key
In above example, table has 4 columns. All the columns have different or unique values. So, any column can be considered as candidate key except the column which has NULL value. So, there are 3 candidate keys in our example table. But, our BID column can’t have NULL value at all. So, BID will be Candidate key (& primary key) & rest all will be alternate keys (if and only if they have unique values).
Composite Key
Composite Key is a key which is combination of more than one field or column of a given table. It can be a Candidate key, Primary key. So, a table with combination of its primary key and candidate key can be called as composite key.
Example of Composite Key
In the example, ID is the primary key and Name is the candidate key which says with the given ID for a NAME can never be repeated. Hence, with respect to Address detail, a person with id 1 and Name AB in ABCD address can’t be repeated. So, both the columns i.e. ID & Name will be considered as composite key of the address (example) table.
Candidate key
It is a key on any single column or multiple columns in a table so as to maintain the row as unique. There can be multiple candidate keys on a single table. If there are multiple candidate keys in a single table, then one will be primary (without NULL value) and rest all will be alternate keys (can have single NULL value).
Candidate keys are those that follow below three rules
1) Unique Value in Table: This rule says that the column which is being considered as candidate key should have unique values.
2) Not Null: The values in the candidate key can’t contain NULL value.
3) Static: The value should be static in the candidate key column so that it should not be changed and will be available as same in all its references or calls.
Example of Candidate Key
In above example, table has 4 columns. All the columns have different or unique values. So, any column can be considered as candidate key except the column which has NULL value. So, there are 3 candidate keys in our example table. But, our BID column can’t have NULL value at all. So, BID will be Candidate key (& primary key) & rest all will be alternate keys (if and only if they have unique values).
Composite Key
Composite Key is a key which is combination of more than one field or column of a given table. It can be a Candidate key, Primary key. So, a table with combination of its primary key and candidate key can be called as composite key.
Example of Composite Key
In the example, ID is the primary key and Name is the candidate key which says with the given ID for a NAME can never be repeated. Hence, with respect to Address detail, a person with id 1 and Name AB in ABCD address can’t be repeated. So, both the columns i.e. ID & Name will be considered as composite key of the address (example) table.