DbMS: SQL Keys

  • Super Key
  • Candidate Key
  • Primary Key
  • Natural Key/Business Key/Domain Key
  • Surrogate Key
  • Unique Key
  • Foreign Key
  • Secondary Key
  • Simple Key

Super Key: Super key is the set of attributes in a table that can identify a row uniquely.

Candidate Key: Candidate key is minimal super key. If any of the attribute, in case of two or more attributes, is removed from the candidate key then uniqueness breaks. N0 candidate key can be NULL. A table can have a multiple candidate keys.

Primary Key: It is one of the candidate keys. Primary key cannot be NULL.

Natural Key:  Natural key is a key composed of columns that actually have a logical relationship to other columns within a table. For example, in case of database of a society, Name, Address is a natural key.

Surrogate Key: Surrogate means substitute, in case natural key is not a good choice, surrogate key is used. Usually column(s) are added for uniqueness of a tuple. For example an ID is provided to every row and this ID is used as primary key. Opposite to natural key, surrogate key does not have logical relationship to other values in a tuple.

Unique Key: (Confused) It is a candidate key. Used to prevent duplicate values in a column. It can be NULL. Multiple unique keys can exist. Unique values can be deleted.

Foreign Key: In the context of relational databases, a foreign key is a field (or collection of fields) in one table that uniquely identifies a row of another table.In simpler words, the foreign key is defined in a second table, but it refers to the primary key in the first table. Foreign key enforces referential integrity:

  • Update to a table having a foreign can happen if the corresponding key exist in the other table.
  • Cascade Delete
  • Cascade Update

Secondary Key: Candidate key(s), not selected as primary key is/are secondary key.

Simple Key: Candidate key with a single attribute is a simple key.