Please login

Prime

Prepinsta Prime

Video courses for company/skill based Preparation

(Check all courses)
Get Prime Video
Prime

Prepinsta Prime

Purchase mock tests for company/skill building

(Check all mocks)
Get Prime mock

Keys in Relational Model

Keys in Relational Model

 

In this article,  we will learn about different types of Keys in Relational Model.

  • Keys ensure the data integrity and consistency and helps to access a record uniquely
  • The relational Model has the following keys
    • Primary Key
    • Foreign Key
    • Candidate Key
    • Alternate Key
    • Super Key

Learn more about Keys here on this page.

Keys in Relational Model

Primary Key

 

A primary key is a constraint  in a table which uniquely identifies each row record in a database table by enabling one or more the column in the table as primary key.

Primary Key in DBMS

Creating a primary key 

A particular column is made as a primary key column by  using the primary key keyword followed with the column name.


CREATE TABLE EMP ( 
  ID   INT     ,      
  NAME VARCHAR (20) ,      
  AGE  INT  ,     
  COURSE VARCHAR(10) ,   
  PRIMARY KEY (ID) 
);
  • Here we have used the primary key on ID column then ID column must contain unique values i.e one ID cannot be used for another student.
  • If you try to enter  duplicate value while inserting in the  row you are displayed with an error
  • Hence primary key will restrict you to maintain unique values and not null values in that particular column

Foreign Key

 

  • The foreign key constraint is a column or list of columns which points to the primary key column of another table 
  • The main purpose of the foreign key is only those values are allowed in the present table that will match to the primary key column of another table.
Foreign Key in DBMS

Example to create a foreign key

Reference Table
CREATE TABLE CUSTOMERS1(
   ID   INT ,            
  NAME VARCHAR (20) ,
COURSE VARCHAR(10) ,    PRIMARY KEY (ID) ); 
 
Child Table
CREATE TABLE CUSTOMERS2(
   ID   INT ,            
   MARKS INT,     
   REFERENCES CUSTOMERS1(ID)
); 

Candidate Key

 

  • Candidate keys are selected from the set of super keys, the only thing that you should remember while selecting candidate keys is, it should not have any redundant attitude
  • Definition of candidate key: Super key with no redundant attributes known as candidate key i.e should not contain any column that contains duplicate data.
Candidate key in DBMS

Example for Candidate Key

  • Consider the student table with columns [ID,NAME,PHONE] 
  • First, identify all the super keys present in the table, then eliminate the super keys that contain a column with duplicate data. Then the remaining superkeys  that are left or nothing but candidate keys
  1. {Id}: ID column will contain all unique values hence ID column is a candidate key
  2. {phone}: As no two students have the same phone number, it is not redundant data column and  hence phone column is a candidate key
  3. {Id, phone}: As both ID and phone are unique for all students this combination is a valid candidate key
  4. {Id, Name}: This combination is not a candidate key because name column may have duplicate values
  5. {Id, phone, Name}: This combination is not a candidate key because name column may have duplicate values
  6. {Name, Phone}: This combination is not a candidate key because name column may have duplicate values

Candidate keys available is the table  student

  • {Id}
  • {phone}
  • {Id, phone}

Alternate Key

 

Alternate keys  are  columns present in the table which are not selected as primary keys but still, they have all the capabilities to be used as a primary key is called alternate key.

Alternate Key in DBMS

Examples for an alternate key

  • Example if a table student contain four columns [ID,NAME,PHONE,AGE ]
  • Among these four columns ID  is used as a primary key because no two  students will have the same Id and capable of uniquely accessing a student record in the table
  • In the same way phone attribute, no two  students will have same phone numberHence  phone column is an alternate key  because it is not been selected as a primary key, even if it is having the capability to be selected as a primary key

Super Key

 

  • A super key is a group of single or multiple keys which uniquely identifies rows in a table. 
  •  A Super key may have additional attributes that are not needed for unique identification.
Super Key in DBMS

Example of the super key

Consider the student table with columns [ID,NAME,PHONE]

    • [ID]: As no two students will have the same Id, it will help to uniquely access the student details, hence it is a super keyNow we will identify all the Super Keys  present in the table that is the  set of attributes that will help to uniquely access a record
    • [NAME, ID]: Even if more than one student has the same name then the combination name will help to recognize the record, as student id will break the tie and this is combination is a super key
  • [PHONE]: As no two students will have the same phone number, a phone number will help to uniquely access the student details and hence phone number is a super key