What are the different types of keys in 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.

1. Candidate key
2. Primary Key
3. Foreign Key
4. Alternate Key
5. 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}

1. Candidate key - student_id OR first_name + last_name
2. Primary Key - student_id
3. Foreign Key - course_id
4. Alternate Key - first_name + last_name
5. Composite Key - first_name + last_name

You may also like - Database Interview Questions Answers

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.

The minimal set of attributes (or attribute) which can uniquely identify the rows (tuples) in a table is known as a 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.

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.

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.

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.

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

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

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

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.

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.

You may also like - Oracle DBA Interview Questions