KEYs in SQL

What are different types of keys?

The super key, Candidate key, Primary key, Alternate or secondary key, foreign key, and composite key.

Candidate Key: The candidate key is a unique key to identify a record uniquely in a table but the candidate key can be multiple in a table. It can contain null values. Candidate key may or may not be a primary key. It helps to signify which key can be used as a candidate key.

A Candidate key is a subset of Super keys and is devoid of any unnecessary attributes that are not important for uniquely identifying tuples.

Alternate or secondary keys: are the candidate keys that are not the primary key.

Primary Key:

Primary key: It is unique + non-null values. It is only 1 per table.

The primary key is a candidate key.

Foreign key:

Foreign key maintains the referential integrity. It generates a relationship between parent and host table.

Super key:

Super key is a single key or multiple keys that can uniquely identify tuples in a table

Composite keys: Composite keys are candidate key or primary key that consists of more than 1 attribute.

Sometimes it is possible that no single attribute will have the property to uniquely identify tuples in a table. In such cases, we can use a group of attributes to guarantee uniqueness. Combining these attributes will uniquely identify tuples in the table.

Points to remember:

• SQL keys are used to uniquely identify rows in a table.
• SQL keys can either be a single column or a group of columns.
• Super key is a single key or a group of multiple keys that can uniquely identify tuples in a table.
• Super keys can contain redundant attributes that might not be important for identifying tuples.
• Candidate keys are a subset of Super keys. They contain only those attributes which are required to uniquely identify tuples.
• All Candidate keys are Super keys. But the vice-versa is not true.
• The primary key is a Candidate key chosen to uniquely identify tuples in the table.
• Primary key values should be unique and non-null.
• There can be multiple Super keys and Candidate keys in a table, but there can be only one Primary key in a table.
• Alternate keys are those Candidate keys that were not chosen to be the Primary key of the table.
• A composite key is a Candidate key that consists of more than one attribute.
• The foreign key is an attribute that is a Primary key in its parent table but is included as an attribute in the host table.
• Foreign keys may accept non-unique and null values.