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|
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 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
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
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 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 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.
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 is same as primary as it is unique and doesn’t allow duplicates but null. Unique key can be simple or composite.
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 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 is a key which is made up of two or more attributes. Example: First Name and Last Name
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.
- 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.