DEV Community

Cover image for Understanding Keys in Relational Databases πŸ”‘
Adhiraj Kinlekar
Adhiraj Kinlekar

Posted on • Updated on

Understanding Keys in Relational Databases πŸ”‘

Keys are used to uniquely identify records or rows of data in a table or to establish and identify relationships between tables. Mainly, there are eight different keys in relational databases.

  1. Super key :Β A Super key is any combination of columns that can be used to uniquely identify a row.

  2. Candidate key : A Candidate Key is the minimal amount of columns that can be used to uniquely identify a row. All super keys can’t be candidate keys but any candidate key can be a super key.

  3. Primary key : A Primary key is a key which uniquely identifies a row. Out of all the Candidate keys that are possible for a table, there can be only one key that can be used as a Primary Key. In other words, a Primary key is the Candidate key selected by the database administrator to uniquely identify rows in a table. A primary key cannot have null or duplicate values.

  4. Alternate key : A candidate key which is not selected as a primary key is called an alternate or a secondary key.

  5. Surrogate key : A surrogate key is a key which is a primary key but the column on which the key is applied is unrelated to the data present in the table. An example of a surrogate key would an auto incrementing Id column which is marked as a primary key.

  6. Composite key : A table can have only one primary key, which may consist of single or multiple fields. When multiple fields are used as a primary key, it is called a composite key.

  7. Foreign key : A foreign key is a column in the table that is the primary key in another table. It is useful for drawing relations between the tables.

  8. Compound key : A compound key is similar to a composite key in that two or more fields are used to uniquely identify a row. However, a compound key is formed when a foreign key is involved in the distinctive identification.

Out of all the keys, three play a significant role in designing a well-organized and robust database system: the primary key, which uniquely identifies records; the foreign key, which establishes relationships between tables; and, while not always chosen as the primary key, candidate keys are vital because they represent the minimal set of columns that can uniquely identify a row.

Song of the day : Cynic - How could I

Top comments (3)

Collapse
 
michaeltharrington profile image
Michael Tharrington

Nice one, Adhiraj! Good resource.

Also, just to say that I'm loving the inclusion of the song of the day throughout your posts! 🎸

Collapse
 
adhirajk profile image
Adhiraj Kinlekar

Thanks Michael, I really appreciate the nice feedback.

Collapse
 
lee_na profile image
Lee

short and precise πŸ‘πŸ½