Primary Key

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

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.

Candidate Key

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

Example :

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

Super Key

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.

Example :

• 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.

• 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.

Composite Key

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.

Foreign Key

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_ID	Department_Name
001	CSE
002	IT
005	Mechnical

Professor Table

Professor_ID	Fname	Lname
P01	Walter	White
P02	Jesse	Pinkman
P03	Skyler	White

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 ID	Department_ID	Fname	Lname
B002	002	Walter	White
B017	002	Jesse	Pinkman
B009	001	Skyler	White