Keys in DBMS

What are Keys in DBMS?

 

In this article, we will learn about Keys in DBMS.

Keys Identify any given tuple or record uniquely and also provide necessary functionality between various tables, in sql there are various types of keys, some of which are –

  • Primary Key
  • Foreign Key
  • Candidate Key
  • Super Key
  • Alternate Key
  • Composite Key

We also use keys as they surely help us in enforcing identity & integrity in the relationship.

Let’s have a look further to understand how each of them work –

Keys in DBMS

How Keys in DBMS work

For an explanation of how each of the keys is working, we will take the help of the following tables –

Student Table

Student_IDnamephoneage
1Arya911112222221
2Bran900001212119
3Jon123456789024
4Cersie987654321024
5Jamie998877665524

Course Table

student_idCourse_IDName
1ECE101Basic Electronics
2CSE101C++
3CSE101C++
1EEE101Basic Electrical
2CSE201DBMS

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

Department_IDDepartment_Name
001CSE
002IT
005Mechnical

Professor Table

Professor_IDFnameLname
P01WalterWhite
P02JessePinkman
P03SkylerWhite

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

Professor_ID IDDepartment_IDFnameLname
B002002WalterWhite
B017002JessePinkman
B009001SkylerWhite