Different Types of Keys Used in Relational Databases

Candidate Key

A candidate key is a minimal set of attributes that uniquely identifies each tuple within a table. In other words, there should not be any two rows in a table that can have the same values for the columns that are the part of candidate key.

Candidate keys are very important for establishing relationships between tables and maintaining data integrity. Candidate keys play a pivotal role in database normalization as they help us to eliminate data redundancy and anomalies.

Unlike a super key, a candidate key may not have any redundant attributes.

Some uses or benefits of candidate keys are:

• Uniqueness and Data Integrity: candidate keys ensure that there are no duplicate tuples in a relation. This prevents data inconsistencies and ensures data integrity.
• Relationship Establishment: Candidate keys are used to establish relationships between tables. For example, in a customer and order scenario, the customer ID can serve as a candidate key in the customer table and as a foreign key in the order table, establishing the relationship between the two tables.
• Indexing and Query Optimization: Candidate keys play a vital role in indexing and optimizing queries. Indexes are created on candidate keys to enhance data retrieval speed. With properly chosen candidate keys, the database management system can efficiently locate the desired data.
• Database Normalization: Candidate keys are closely associated with database normalization. By identifying candidate keys and eliminating redundancy, a database can achieve higher levels of normalization, reducing data redundancy and anomalies.

Primary Key

A primary key in a table is an attribute or tuple of attributes that uniquely identifies each row and column or set of columns in the table. The attributes in a primary key are known as prime attributes.

The primary key enables us to uniquely identify each record in the relational table.

The primary key cannot have value NULL.

Characteristics of the Primary Key

• Minimal: The primary key is composed of a minimum number of attributes that satisfy the unique occurrence of the tuples or records. So if one candidate key is formed using two attributes and the other using a single attribute then the one with a single attribute key should be chosen as the primary key.
• Accessible: The primary key is used to check the ability to access and interact with the database. The user must easily create, read or delete a tuple using it.
• Non-NULL Value: The primary key which refers to a nonnull value that is required for the identification of the tuple (record) means that any attribute can not contain the non-null value in DBMS.
• Time Invariant: The values of the primary key must not change or become null during the lifetime of the relation.
• Unique: The primary key in which value can not be duplicated in any of the rows or tuples of the relation.

main_id INTEGER PRIMARY KEY;

CREATE TABLE table_name (
   column_1 INTEGER NOT NULL,
   column_2 INTEGER NOT NULL,
   ...
   PRIMARY KEY(column_1, column_2,...)
);

Super Key

A super key is a non-minimal candidate key, that is a candidate key plus one or more redundant attributes. Here redundant means not needed for unequivocally identifying each record. Therefore a super key is not a candidate key.

Alternate Key

An alternate key is any candidate key not selected as the primary key. So, while a table may have multiple candidate keys (sets of columns that could uniquely identify rows), only one of them is designated as the Primary Key. The rest of these candidate keys become Alternate Keys.

In other words, we can define the Alternate key as the set of Candidate Keys other than the Primary Key.

Alternate Keys in SQL are defined using the SQL constraint UNIQUE:

UNIQUE(col_name(s))

where col_name(s) is the name(s) of the column(s) in the table which need to be unique. Unfortunately, this constraint allows for redundant attributes. In that case we will be defining a super key, not a candidate key.

Foreign Key

A foreign key is a column or set of columns in one table that references a uniquely-valued set of columns (primary key) of another table. This creates a relationship between the two tables, ensuring that the child table (which contains the foreign key) can only have values that exist in the parent table's column(s).

The table containing the foreign key is called the foreign table (or child table), and the table that the foreign key references is called the primary table (or parent table).

The primary purpose of a foreign key is to maintain referential integrity, ensuring that the relationship between tables is consistent and that invalid data does not enter the system.

A foreign key could serve as the primary key of another relationship to which it is connected through a relationship.

Composite Key

A composite key is a primary key that consists of two or more columns in a database table. It is used when a single column cannot uniquely identify a record, but the combination of multiple columns can.

Surrogate Key

A surrogate key is a manufactured unique identifier created in each database table record. When a natural primary key is not available, this value, which is usually an integer or a system-generated number, is utilized.

Natural Key

A natural key is a column or a set of columns in a database table that has inherent meaning and can uniquely identify a record. It is derived from the data itself and is not an artificially generated value.