What are the different types of keys in RDBMS

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

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 - MongoDB Interview Questions Answers

Example:

STUDENT - {STUD_ID, FIRST_NAME, LAST_NAME, COURSE_ID}

STUDENT table keys are:

Candidate keys are STUD_ID or FIRST_NAME+LAST_NAME

Primary Key: STUD_ID

Foreign Key: COURSE_ID

Alternate Key: FIRST_NAME+LAST_NAME

Composite Key: FIRST_NAME+LAST_NAME

Candidate keys are those keys which are a candidate for the primary key of a table. In simple words, we can understand that such type of keys which full fill all the requirements of the primary key which is not null and have unique records is a candidate for a primary key. So this type of key is known as a candidate key. Every table must have at least one candidate key but at the same time can have several. Such type of candidate key which is chosen as a primary key for the table is known as a primary key. Primary keys are used to identify tables. There is only one primary key per table. In SQL Server when we create a primary key to any table then a clustered index is automatically created to that column.
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 those keys which are used to define a relationship between two 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.  a foreign key is generally a primary key from one table that appears as a field in another where the first table has a relationship to the second. 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.

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

For a Db-Table, there are so many keys exist and might be eligible for Primary Key. So that all keys, primary key, unique key, etc are collectively called as Candidate Key. However, DBA selects a key from candidate key for searching records is called Primary key. 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. Like here we can take a very simple example to understand the concept of alternate key. Suppose we have a table named Employee which has two columns EmpID and EmpMail, both have not null attributes and unique value. So both columns are treated as a candidate key. Now we make EmpID as a primary key to that table then EmpMail is known as an alternate key. When we create keys on more than one column then that key is known as the composite key. Like here we can take an example to understand this feature. I have a table Student which has two columns Sid and SrefNo and we make the primary key on these two columns. Then this key is known as a composite key.

You may also like - MySQL Interview Questions Answers

A table can have multiple unique and foreign keys. However, 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.