Keys in DBMS

To uniquely identify each tuple or row in a data table. We use primary key as identifying attribute.

For example –

We all are given a unique rollnumber or registration number at the time of admission in a school or college. That is primary key for any student table.

In the example mentioned above STUDENT_ID works as primary Key.

The minimal (minimum) number of attributes that can uniquely identify a record for a data table is/are candidate key(s). 

For example –

• STUDENT_ID can identify a unique record in a datable
• For course table we need both STUDENT_ID and COURSE_ID to uniquely identify a record i.e. (STUDENT_ID, COURSE_ID) become candidate key, which is composite in nature.

1. Candidate keys must have non null elements in each record/tuple
2. Only Unique values are allowed
3. Candidate key may have multiple attributes
4. It should contain minimum fields to ensure uniqueness
5. Should be able to uniquely identify each record in a table

Set of attributes that can help us uniquely identify a record or tuple in the database is called super key.

Now, super key may sound the same as candidate key. Yes, it does !!! There is only slight different.

• Candidate key says – Minimal
• Super key – Doesn’t say any number

Thus, for course table STUDENT_ID, COURSE_ID, NAME can be a super key of 3 composite attributes in nature, as they will uniquely identify the record.

But, it is not a candidate key as just by using STUDENT_ID and COURSE_ID we can uniquely identify rows. Thus, minimum number is 2.

Note –

• Every Candidate key is a super key
• The vice versa is not true however
• If we just add one or more attributes to a candidate key then, it becomes a super key.

There can be multiple Candidate keys right? Out of which we may select one as a primary keys.

The other set of candidate keys than primary key itself are known as candidate keys.

Keys which more than one attribute that can uniquely identify a record in a table is a composite key.

Note – Some people may get confused between candidate key and composite key –

Candidate Key: A nominee for primary key field is known as candidate key.

Composite Key: Creating more than one primary key is jointly known as composite key.

A candidate key is a unique key that can be used as a primary key. Composite key is a key of two or more attributes that uniquely identifies the row. A key is a set of columns that can be used to uniquely identify each row within a table.

To define a relationship with another table of a database. We use foreign key. A foreign key is a column of a table that points towards the primary key of another table.

For example –

Department Table

Professor Table

In the above tables Department_ID is the primary key for Department Table and Professor_ID is the primary key of Professor Table.

Now, if we add DeptarmentID in table 2 in that case we can create a relationship between table 1 and table 2. Understanding that which teacher belongs to which department. Thus adding Department_ID, which is foreign key in New table and the primary key Department table.

New Table