Keys And Its Types

  • A Key is an attribute in the table.
  • it is mostly use to uniquely identify the tuple in the table.
  • Key is also use to establish the relation between the tables.

Types Of Keys 

There are various key’s in database which are explained under,  

1. Primary key

  • The Primary key is an attribute which always be unique and never be a null in the table.
  • Primary key is use to uniquely identify each tuple (instance) in a table.
  • More than one primary key cannot be used as primary key because when only one key is enough to uniquely identify the tuple then no need to use second primary key.
  • Primary key is mostly assign by department. For example, student RollNo, or Student_ID must be assign by department.
  • For example: In the Student table, Student_id is best suited for the primary key. Rest of the attributes like Email and CNIC can also be used as primary key but First_Name and Last_Name cannot use as primary key because these attributes may be same for other tuples of the table.

2. Composite Key in RDBMS

  • If Primary Key is construct with two or more attributes then it is called Composite Key.
  • Composite key is required when a single attribute is not enough to uniquely identify the each tuple in the table.

Example: In the following table Composite Key { Order_ID, Product_ID }

3. Candidate key

  • A set of minimal attributes that can uniquely identify each tuple in the given table is known as a candidate key.
  • Candidate key is also unique key like primary key but it can be a null, that’s why it cannot be used as primary key.
  • Every table must have one or more candidate keys in the table.

For example: In the Student table candidate key are Student_ID, CNIC and Email.

Difference between Primary Key and Candidate Key

Primary Key Candidate Key
Primary key column cannot have null value. Candidate key column can have null value.
Only a single primary key is present table Multiple candidate keys may present in table
Every Primary key is a candidate key. Every Candidate key may or may not be use a primary key

4. Super Key

  • It is a set of attributes which can uniquely identify each tuple in the relation.
  • So, Super key is a combination of atleast one candidate key and one other key.
  • We can say, Super key is a super-set of a candidate key.
  • A super-key is not restricted to some specific number of attributes.

For example: In the following Student table super keys can be of the following sets,

  • {Student_ID}
  • {First_Name, Last_Name, CNIC,}
  • {Student_ID, First_Name, Last_Name, Email}
  • {Student_ID, First_Name, Last_Name, CNIC, Email}

Some more super key set also possible which can uniquely identify the tuple.

 

Keep in Mind: Attributes “First_Name” and “Last_Name” cannot be use as candidate key because these attributes cannot uniquely identify the tuple.

Tip: Primary, composite, candidate and Super keys can identify the each tuple uniquely.

5. Alternative-Key

  • All candidate keys except primary key are called alternative keys

In the following example , Alternate keys Attributes are CNIC and Email.

 

6. Foreign key

  • Foreign key is an attribute or set of attributes which are use to point the primary key of another table or same table.
  • More than one Foreign keys from different table can point to single primary key

Explanation: The table which contains the primary key is known as Referenced Table while the other table which contain the foreign key is known as referencing table.

Important Note:  Foreign key column of referencing table can fetch the any corresponding value from referenced table. 

For Example: Consider the Student-Detail and Student-Marks table as given below.

The Names of Primary Key Column and Foreign Key Column may or may not be similar but values of foreign key column and Primary key column will always be similar.

 Reference key can be added after creating the table.

7. Partial Key

  • Partial key is an attribute which can select multiple tuples at a time but cannot identify each tuple uniquely. 

Example: Consider the following schema

Student ( Std_Name , Std_course , Std_department)

Here, by using partial key (Std_departnment), we cannot identify any tuple uniquely but we can select some tuples from the table.

8. Unique Key

Unique key column contains the following properties

  • All values of unique key column will be unique in the table.
  • It is non-updatable because Once the value is assigned then it will never be changed.
  • It may have a NULL value.

Example: The best example of unique key is CNIC

  • CNIC number is unique for all the citizens (rows) of Pakistan (table).
  • If CNIC gets lost or expire then then the duplicate CNIC always has the same number as before. So it is non-updatable.
  • Thus, it is non-updatable.
  • Few citizens may not have their CNIC number. So this field may be a null value.

9. Surrogate Key

Surrogate key column contains the following properties

  • All values of Surrogate key column will be unique in the table.
  • It is updatable because Once the value is assigned then it can be changed
  • It cannot have a NULL value.

Mobile Number of students is best example of surrogate key but condition is that every student must hold its own mobile phone number.

10. Secondary Key

Secondary key required in indexing where better and faster searching is required.