Types of Keys

There are many types of keys associated with database management. Out of that, the most important and necessary keys are discussed in this tutorial

Super Key:

A combination of one or more attributes, with which we can uniquely identify a particular entity is known as a super key.

For Example: If we consider the student schema as

student(roll no,name,sex,salary,age,address,class,section)

(class,section,roll no)

(class,section,roll no,sex)

(name, address) are the list of superkeys, that can uniquely identify the student.

Candidate Key:

The candidate key can be defined as the minimal super key.

From the above example, we can say that (class, section, roll no, sex) will be the candidate key with minimal superkey, ensuring the sufficient and essential attributes to uniquely identify the entity. Similarly, name and address can also be identified as a candidate key.

Primary Key: 

Out of the identified candidate keys, implemented candidates can be called as the primary key.

From the above example (class, section, roll no) can be implemented as a primary key.

Alternate Key:

The unimplemented candidate key is known as alternate key.

So, from the above example (name, address) can be known as the alternate key.

Foreign Key:

The attribute which is a prime attribute in one table but non-prime attribute in another table is known as the foreign key.

For example: If we consider the tables in a university database

Student(roll, name, sex, age, address, class, section)

Department(deptno, deptname)

Teacher(tno, tname, tage, deptno)

Dependent(empno, dependentname, relation)

Employee(empno, empname, age, sex)

Out of the above tables, the underlines attributes are called the prime or key attributes and the remaining are known as non-prime attributes. In the above database, the deptno is the foreign key in the Teacher table with deptno as a prime attribute in the Department table.

When a foreign key occurs then domain constraint comes into play. It means that the set of values assigned for the primary key in both the tables must be in the same domain.

Partial Key: 

The attribute which can relate the set of tuples, if no primary key is identified. In the above database for the Dependent table, empno can be known as the partial key.

Secondary Key: 

The secondary key is mainly used for the Indexing, for better-searching performance.

Unique Key:

The unique key must be unique to be identified and cannot be updatable but it can be null.

Surrogate Key:

This is known as the unique key to be identified and can be updatable but the null value is not allowed.

Composite Key:

If any primary key made up of more than one attribute then it is known as a composite key. 

For prime attribute, not null, non-updation constraints will be applicable.

This is all about the types of keys. Visit the sections of Data Science for more information.