When designing database several types of keys are selected to support various requirements.

Following are types of keys in DBMS

Before we proceed let’s be clear about terminology . Following words can be used interchangeably

SQL Terms	Relational Database Terms
Row	Record or Tuple
Column	Field or Attribute
Table	Relation

Key

The key in the relational database is a normal attribute(field) or group of attributes that can be used either uniquely identify or sort records(tuple) or row in a table(relation).

Primary Key

Primary key is the key that is a candidate key selected by Database designer from available candidate keys. Primary is used to uniquely identify each row in the table. Primary key should be unique and cannot be null. Example: AUTO INCREMENT id, username, product id etc

Natural key

A natural key is a key that naturally determines/identifies each row in the table. Natural key can be chosen as primary key. It is formed by attributes that exist in the real world outside database. Natural key is also called as Business Key or Domain Key

Example: invoice-id, email-id

Surrogate Key

A surrogate key is a key which is artificially generated to uniquely identify each row in the table. Often when no key is available to uniquely identify rows in the table, surrogate key is chosen as primary key. Example: AUTO INCREMENT id, UUID, transaction id etc

Foreign Key

Foreign Key is a primary key of one table located in the other(foreign) table. Foreign keys are used to establish a relation between tables and they pop-up during normalization. Foreign keys play vital role in referential integrity.

Candidate key

Candidate key is an attribute or set of attributes that uniquely identifies each tuple(row) in the relation(table). A candidate key can be simple or composite. Primary key is selected from candidate keys available.

Super Key

A super key is a set of attribute that identifies each tuple(row) uniquely in the given relation(table). If you add any attribute to a candidate key what you get is super key.

Unique Key

Unique key is same as primary as it is unique and doesn’t allow duplicates but null. Unique key can be simple or composite.

Secondary Key

Unlike candidate key secondary key is used for indexing and retrieval purpose. Secondary key can be simple or composite. Example: Last Name, Phone Number

Alternate Key

Alternate key is a candidate key which is not selected as primary key. All candidate keys which are not selected as primary keys are alternate keys.

Composite Key

Composite key is a key which is made up of two or more attributes. Example: First Name and Last Name. It is also referred as concatenated key

Compound Key

Both composite key and compound key means the same thing that a candidate key with two or more attributes. But, compound key is mostly used in the context of ER modeling . Where, compound key is a composite key with each attribute that make up the key is simple key.

Summary

• Key is any attribute in the relation that is used either to sort or uniquely identify each row
• Candidate Key is a key which doesn’t contain duplicates and identifies each row uniquely
• Primary key is selected from candidate keys available in the table
• All candidate keys which are not selected as primary key are called alternate keys
• Super key is a super set of candidate key. If you add any attribute to candidate key you will get super key
• A natural key is a key is formed by attributes that naturally exist in the real world outside the database. Example: roll no.
• Secondary key is an attribute that is strictly used for retrieval purpose.
• Composite key made up of two or more attributes.