Keys in Dbms

Key
• A key in DBMS is an attribute or a set of attributes that help to uniquely

identify a tuple (or row) in a relation (or table).
• Keys are also used to establish relationships between the different tables
and columns of a relational database.
• Individual values in a key are called key values.
Keys
• A key could either be a combination of more than one attribute (or columns) or
just a single attribute. The main motive of this is to give each record a unique
identity.
There are broadly seven types of keys in DBMS:
1.Super Key
2.Candidate Key
3.Primary Key
4.Composite Key
5.Alternative Key
6.Artificial Key
7.Foreign Key
Super Key
• Super Key is the set of all the keys which help to identify rows in a
table uniquely.
• This means that all those columns of a table that are capable of
identifying the other columns of that table uniquely will all be
considered super keys.
Super Key
• Super keys are those attributes of relation which have properties of

uniqueness.
Example: Consider
Student(roll number, name class)
Roll number has a property of uniqueness.
There are a number of other combinations:
a. roll number, name
b. roll number, class
c. roll number, name, class
Candidate Key
• Candidate keys are those attributes that uniquely identify rows of a

table.
• The Primary Key of a table is selected from one of the candidate keys.
• Candidate keys are defined as the set of fields from which the
primary key can be selected.
• It is an attribute or set of attributes that can act as a primary key for a
table to uniquely identify each record in that table.

Candidate Key
• A candidate key is a field or combination of fields that can act as a

primary key field for that table to uniquely identify each record in
that table.
• It can be defined as minimal Super Key or irreducible Super Key.
• In other words an attribute or a combination of attributes that

identifies the record uniquely but none of its proper subsets can
identify the records uniquely.
Candidate Key
Student(ID, Name, Address)

• For the above, we have only two Candidate Keys (i.e. Irreducible Super
Key) used to identify the records from the table uniquely.
1. ID can identify the record uniquely
2. combination of Name and Address can identify the record uniquely, but
neither Name nor Address can be used to identify the records uniquely,
as it might be possible that we have two employees with similar name or
two employees from the same house.
Primary Key
• A primary key is a column of a table or a set of columns that helps to

identify every record present in that table uniquely.
• There can be only one primary Key in a table.
• Also, the primary Key cannot have the same values repeating for any
row.
• Every value of the primary key has to be different with no repetitions.
Example: Primary Key
Database designer can use one of the Candidate Key as a Primary Key. In
this case “ID” and “Name, Address” are Candidate Key, from which
consider “ID” Key as a Primary Key as the other key is the combination of
more than one attribute.
Alternate Key
• A table can have multiple choices for a primary key; however, it can
choose only one.
• So, all the keys which did not become the primary Key are called
alternate keys.
• (The candidate key which are not selected for primary key are known
as secondary keys or alternative keys)
• Alternate Key can be any of the Candidate Keys except for the Primary
Key.
• E.g. of Alternate Key is “Name, Address” as it is the only other
Candidate Key that is not a Primary Key.
Foreign Key
• Foreign Key is used to establish relationships between two tables.

• A foreign key will require each value in a column or set of columns to
match the Primary Key of the referential table.
Foreign Key
• Usually a foreign key in one table refers to the primary key (PK) of
another table. This way references can be made to link information
together and it is an essential part of database normalization
• The purpose of the foreign key is to ensure referential integrity of the
data i.e. only values that are supposed to appear in the database are
permitted
Foreign Key
Let consider
Department(Department_ID, Department_Name, Manager_ID,
Location_ID) with Department_ID as an Primary Key.
Employee (Emp_id, Emp_name, Department_ID)
can be defined as the Foreign Key as it can refer to the Department_ID
attribute of the Departments table (the referenced or parent table), a
Foreign Key value must match an existing value in the parent table or
be NULL.
Composite Key
• Composite Key is a set of two or more attributes that help identify

each tuple in a table uniquely.
• The attributes in the set may not be unique when considered
separately.
• However, when taken all together, they will ensure uniqueness.
Composite Key
• If we use multiple attributes to create a Primary Key then that Primary

Key is called Composite Key (also called a Compound Key or
Concatenated Key).
• But any attribute that makes up the Composite key is not a simple key
in its own.
• E.g. of Composite Key, if we have used “Name, Address” as a Primary
Key then it will be our Composite Key.
Unique Key
• Unique Key is a column or set of columns that uniquely identify each

record in a table.
• All values will have to be unique in this Key.
• A unique Key differs from a primary key because it can have only one
null value, whereas a primary Key cannot have any null values.

Keys in Dbms

Uploaded by

Copyright:

Available Formats

Keys in Dbms

Uploaded by

Document Information

Original Description:

Copyright

Available Formats

Share this document

Share or Embed Document

Sharing Options

Did you find this document useful?

Is this content inappropriate?

Copyright:

Available Formats

Keys in Dbms

Uploaded by

Copyright:

Available Formats

Key

• A key in DBMS is an attribute or a set of attributes that help to uniquely

• Super keys are those attributes of relation which have properties of

• Candidate keys are those attributes that uniquely identify rows of a

• A candidate key is a field or combination of fields that can act as a

• It can be defined as minimal Super Key or irreducible Super Key.

• In other words an attribute or a combination of attributes that

Student(ID, Name, Address)

• A primary key is a column of a table or a set of columns that helps to

• Foreign Key is used to establish relationships between two tables.

• Composite Key is a set of two or more attributes that help identify

• If we use multiple attributes to create a Primary Key then that Primary

• Unique Key is a column or set of columns that uniquely identify each

You might also like