Keys in Relational Model

February 1, 2023

Keys in Relational Model

On this page, 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

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.

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.

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)
);

What is Database

Participation Constraint

DBMS Architecture

RDBMS

Cardinality

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.

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

{Id}: ID column will contain all unique values hence ID column is a candidate key
{phone}: As no two students have the same phone number, it is not redundant data column and hence phone column is a candidate key
{Id, phone}: As both ID and phone are unique for all students this combination is a valid candidate key
{Id, Name}: This combination is not a candidate key because name column may have duplicate values
{Id, phone, Name}: This combination is not a candidate key because name column may have duplicate values
{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.

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 number. Hence 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.

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

Prime Course Trailer

Related Banners

Get PrepInsta Prime & get Access to all 200+ courses offered by PrepInsta in One Subscription

Get over 200+ course One Subscription

Courses like AI/ML, Cloud Computing, Ethical Hacking, C, C++, Java, Python, DSA (All Languages), Competitive Coding (All Languages), TCS, Infosys, Wipro, Amazon, DBMS, SQL and others

Keys in Relational Model