Keys in RDBMS

The keys in RDBMS play a significant role in organizing and managing data. We will look at some of the important keys in RDBMS which will help us in designing our database.

Introduction to Keys in RDBMS

Keys are very important in helping us organize and manage databases.

A key in RDBMS is a column or a group of columns that uniquely identifies a row in a table. Keys are mainly used to identify records and establish relationships between different tables.

Super Key

A super key is a set of one or more columns in a table that can uniquely identify each row in the table.

It is used to identify possible combinations of columns which can be used to uniquely identify rows in a table.

Super Key is an available column or a combination of columns, which identifies each row uniquely.

Let's see an example of the column names of a table 'Employee'.

1. Employee_Id

2. Employee_Name

3. Employee_panNo

4. Employee_licenseNo

The super key in this table is (Employee_Id, Employee_panNo, Employee_licenseNo).

Here Employee_Name cannot be super key because it will not uniquely identifies each row(Name can be duplicated). But it can be combined with the above super keys.

Now available super key is (Employee_Id, Employee_panNo, Employee_licenseNo, Employee_Id+Employee_Name, Employee_panNo+Employee_Name, Employee_licenseNo+Employee_Name)

Candidate Key

A candidate key is similar to super keys but in these keys, every key even in combination also, uniquely identifies each row.

Let's see a table of the following columns.

1. Employee_Id

2. Employee_Name

3. Employee_panNo

4. Employee_licenseNo

In this table candidate keys can be:

Employee_Id, Employee_panNo, Employee_licenseNo,

Employee_Id+Employee_panNo, Employee_Id+Employee_licenseNo

Primary Key

The primary Key is a selected candidate key that identifies each record uniquely.

A Primary Key can be only one in any table.

Let's see a table of the following columns.

1. Employee_Id

2. Employee_Name

3. Employee_panNo

4. Employee_licenseNo

In this table, only three single candidate keys identify rows uniquely that are Employee_Id, Employee_panNo and Employee_licenseNo. But the Primary key will be only one candidate key which will identify each record uniquely. So in this table, Employee_Id is a primary key.

Foreign Key

A Foreign Key is a primary key of another table that is related to the first table.

Let's see a relational table.

Table Name: Employee

1. Employee_Id

2. Employee_Name

3. Employee_panNo

4. Employee_licenseNo

5. Employee_Department

Table Name: Department

1. Department_Id

2. Department_Name

3. Department_MemberNo

In the above example, we can see two relational tables. In a company, every employee must be associated with a department and every department must be included at least one employee.

Employee_Id will be the primary key in the first table and Department_Id will be the primary key in the second table. The second table's primary key will be the foreign key for the first table. So Department_Id is the foreign key for the table 'Employee'.

Composite Key

When only one primary key is not available in any table or in other words, only one column can not uniquely identifies rows in a table, then we use more than one column to make the primary key.

For example, look at this table.

Table Name: Product

1. Order_Id

2. Product_Name

3. Customer_Id

4. Product_code

In the above example, Any single column can not be a primary key of this table. So the combination of columns will be the primary key. In this table, Order_Id+Customer_Id+Product_code is used as a primary key of this table that is composite Key.

Artificial Key

If the composite key will become lengthy words, then we use the artificial key.

Let's see this table's column name.

Table Name: Product

1. Order_Id

2. Product_Name

3. Customer_Id

4. Product_code

With the help of a composite key, we create a combination of columns 'Order_Id+Customer_Id+Product_code' but it is a long word. So we make an artificial key to recognize records uniquely.

In this table, a new column 'id', can be added to identifies records uniquely.

So the new columns of this table are:

1. Id

2. Order_Id

3. Product_Name

4. Customer_Id

5. Product_code