KEYS in RDBMS

 What are keys in DBMS ?

      A key in DBMS is an attribute or a set of attributes that help to uniquely identify a row in a relation of table. Keys are also used to create relationships between the different tables and columns of a relational database .

There are different types of keys as shown in above figure

Super Key

     A super key is a set of one or more attributes (columns), which can uniquely identify a row in a table. Super key is a superset of a candidate key.

FOR example:- 

  In EMPLOYEE table,  the name of two employees can be the same, but their EMPLYEE_ID can't be the same. Hence, The super key would be EMPLOYEE-ID.

candidate key

 A super key with no redundant attribute is known as candidate key . The remaining attributes except for primary key are considered as a candidate key. The candidate keys are as strong as the primary key.

FOR example:-

 In the EMPLOYEE table, id is best suited for the primary key. Rest of the attributes like SSN, Passport Number, and License Number, etc. are considered as a candidate key.

Primary key

  It is used to identify one and only one instance of an entity uniquely . It is a column or set of columns in a table that uniquely identifies rows in that table.

FOR example

In the EMPLOYEE table, ID can be primary key since it is unique for each employee. In the EMPLOYEE table, we can even select License Number and Passport Number as primary key since they are also unique.

Foreign key

  It is also like a primary key just the difference is that it is for the column. Foreign keys are the columns of a table that points to the primary key of another table. They act as a cross-reference between tables.

FOR example

  In a company, every employee works in a specific department, and employee and department are two different entities. So we can't store the information of the department in the employee table. That's why we link these two tables through the primary key of one table.

Composite key

   A key that has more than one attributes is known as composite key. It is also known as compound key. but when it taken individually it does not guarantee uniqueness. Composite key is a key which is the combination of more than one field or column in a table. It may be a candidate key or primary key.

FOR example

Alternate key

     A table can have many candidate keys. Among these candidate keys, only one key can be used as primary key a table can have multiple candidate keys. With these candidate keys, only one key gets selected as primary key, the remaining keys are known as alternative or secondary keys. key, the remaining keys are known as alternative or secondary keys.

FOR example 

 EMPLOYEE table can have the following attributes: 

Employee SSN: The SSN number of employees is stored in this field.

Employee Id: An attribute that stores the value of the employee identification number.

Employee name: An attribute that stores the name of the employee holding the specified employee id.

Employee email: An attribute that stores the email id of the specified employees.

CANDIDATE key will be as follows:

Employee SSN

Employee ID

Employee email

 From the above key only one can be selected as primary key and other two will be the alternate key

Employee ID will be the primary key (which we have discussed before)

other employee ID and employee email both will be used as alternate key .

" In this blog we have discussed six keys that are used at the basics "