Dr. E.F. Codd has illustrated following types of keys in RDBMS.
- Candidate Key
- Super Key
- Primary Key
- Secondary Key or Alternative Key
- Simple Key
- Composite Key
- Compound Key
- Foreign Key
A candidate key is defined as the least combination of fields that uniquely identifies each record in the table. Every table must have at least one candidate key.
- In order to be eligible for a candidate key it must pass certain criteria.
- It must contain unique values
- It must not contain null values
- It contains the minimum number of fields to ensure uniqueness
- It must uniquely identify each record in the table
A Super key is any combination of columns within a table that uniquely identifies each record within that table. Any combination of column with a Candidate key makes a Super key.
A primary key is a candidate key that is most appropriate to be the main reference key for the table.
Secondary Key or Alternative Key
A table may have one or more choices for the primary key. Collectively these are known as candidate keys as discuss earlier. One is selected as the primary key. Those not selected are known as secondary keys or alternative keys.
If any of the key column (Candidate key or Primary key) is a single columns then that can be called as a Simple key.
When atleast two or more columns combine together to uniquely identify a row then that combination of columns can be referred as composite key. In other words, If any of the key column (Candidate key or Primary key) is composed of more than one column, then the combination of columns can be called as composite key.
Compound key is a key that consists of 2 or more attributes that uniquely identify an entity occurrence. Each attribute that makes up the compound key is a simple key in its own right.
A foreign key is generally a primary key from one table that appears as a field in another table where the first table has a relationship to the second.