Keys in Database Management Systems

Different types of keys:

There are the following different type of keys in DBMS

Super Key:

A super key is a set of one or more than one instance of an entity that can be used to identify records uniquely. Primary key, unique key and alternate key are subset of super key. See blow table for more understanding.

Candidate Key:

A candidate key is a set of one of more than one instance of an entity that can be used to identify the unique records/data. There can be multiple candidate keys in an entity. In the blow table ID, Enroll ID are candidate keys, since these keys can be work as primary keys.

Primary Key:

Primary key is set of one or more than one instance in the entity that uniquely identify the records in the database. I can’t be NULL or have any duplicate value. Only one candidate key can be a primary key. In the blow table ID is a primary key, but it also a candidate key.

Alternate Key:

All alternate keys are the keys that can work as primary keys. Basically it is a candidate key that is not currently primary key. In the blow table Enroll ID, Phone no. are the alternate keys because they are not primary keys, currently. So alternate keys are those who can be a primary key.

Composite/Compound Key:

Compound key is a combination of more than one key. It may be candidate key and primary key. In the blow table we can make a compound key with combination of two candidate keys i.e. {ID, Enroll ID}

Unique Key:

Unique key is a set of one or more than one instance of entity that can identify unique records from database. It is like primary key but only accept one null value. I can’t have duplicate value. Unique key can be added multiple times in an entity. You can see in the blow table there are two unique keys -- Enroll ID, Phone no.

Foreign Key:

Foreign key is a field in the database that is the primary key in the other table. It can accept multiple NULL, duplicate values in the table. We can have Dep_ID in the tbl_department as shown blow and we are using it as foreign key in the tbl_enrolledStudents.

Table 1 tbl_department