What are the different types of keys in RDBMS

Author Full Stack Tutorials
Last Updated - December 29, 2019

What are the different types of keys in RDBMS, Keys: RDBMS | Primary Key | Foreign Key | Composite Key | Candidate Key

Different types of keys - RDBMS

We know that database uses tables to organize information. To maintain data integrity (that is data should be correct and in well-formed) we use the concept of keys.

There are five different types of keys in RDBMS which are as follows -

Candidate key
Primary Key
Foreign Key
Alternate Key
Composite Key

You may also like - MySQL Interview Questions Answers

Example: Suppose we have a Student Table.

Student - {student_id, first_name, last_name, course_id}

Student Table Keys:

Candidate key - student_id OR first_name + last_name
Primary Key - student_id
Foreign Key - course_id
Alternate Key - first_name + last_name
Composite Key - first_name + last_name

You may also like - Database Interview Questions Answers

1. Candidate Key

A candidate key is a set of attributes (or attribute) which uniquely identify the rows (tuples) in table.

Every table must have at least one candidate key but at the same time can have several.

2. Primary Key

The minimal set of attributes (or attribute) which can uniquely identify the rows (tuples) in a table is known as a primary key.

Key Points about Primary Key

Primary key is use to identify each row uniquely in a table.
Primary key cannot have a NULL value.
Each table can have only one primary key.
By default, Primary key is clustered index and data in the database table is physically organized in the sequence of clustered index.
Primary key can be related to another table's as a Foreign Key.
We can generate ID automatically with the help of Auto Increment field. Primary key supports Auto Increment value.

3. Foreign Key

A foreign key is a field in table which is generally a primary key from another table.

In other words, if we had a table A with a primary key X that linked to a table B where X was a field in B, then X would be a foreign key in B.

A foreign key is those keys which are used to define a relationship between two or more tables.
When we want to implement a relationship between two tables then we use the concept of foreign key.
It is also known as referential integrity.
We can create more than one foreign key per table.

Difference between Primary Key and Unique Key?

As per RDBMS prospective, Key(a.k.a Candidate Key) is a value or set of values that uniquely identifies entity.

1. Behavior: Primary Key is used to identify a row (record) in a table whereas Unique-key is to prevent duplicate values in a column (with the exception of a null entry).
2. Indexing: By default Sql-engine creates Clustered Index on primary-key if not exists and Non-Clustered Index on Unique-key.
3. Nullability: Primary key does not include Null values whereas Unique-key can.
4. Existence: A table can have at most one primary key but can have multiple Unique-key.
5. Modifiability: You can’t change or delete primary values but Unique-key values can.

4. Alternate Key

If any table has more than one candidate key, then after choosing a primary key from those candidate key, rest of candidate keys are known as an alternate key of that table.

5. Composite Key

When we create keys on more than one column then that key is known as the composite key.

Suppose I have a table Student which has two columns student_id and student_ref_no and we make the primary key on these two columns. Then this key is known as a composite key.

You may also like - MongoDB Interview Questions Answers

Different types of keys - RDBMS - Tricky Questions

Q:- Can a table have multiple unique, foreign, and/or primary keys?

A table can have multiple unique and foreign keys. But, a table can have only one primary key.

Q:- Can a unique key have NULL values? Can a primary key have NULL values?

Unique key columns are allowed to hold NULL values. The values in a primary key column, however, can never be NULL.

Q:- Can a foreign key reference a non-primary key?

Yes, a foreign key can actually reference a key that is not the primary key of a table. But, a foreign key must reference a unique key.

Q:- Can a foreign key contain null values?

Yes, a foreign key can hold NULL values. Because foreign keys can reference unique, non-primary keys – which can hold NULL values – this means that foreign keys can themselves hold NULL values as well.