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