Types Of Keys in DBMS

“TRIBHUVAN UNIVERSITY”
DEPARTMENT OF PUBLIC ADMINISTRATION
PUBLIC ADMINISTRATION CAMPUS
PRESENTATION
DIFFERENT TYPES OF KEYS
IN DATABASE
SUBMITED BY:
PADAM NEPAL
029/074
6TH SEM

TABLE OF CONTENT
• KEYS
– TYPES OF KEYS
I. SUPER KEY: EXAMPLE
II. CANDIDATE KEY: PROPERTIES/DIFFERENCE
III. PRIMARY KEY: EXAMPLE/RULES
IV. ALTERNATE KEY
V. UNIQUE KEY
VI. COMPOSITE KEY
VII. FOREIGN KEY
VIII.NATURAL KEY
IX. SURROGATE KEY
X. CONCLUSION

KEYS
• KEYS in DBMS is an attribute or set of attributes which helps you to
identify a row(tuple) in a relation(table).
• They allow you to find the relation between two tables.
• Keys help you uniquely identify a row in a table by a combination of one or
more columns in that table.
• Key is also helpful for finding unique record or row from the table.
• It is used to fetch or retrieve records / data-rows from data table
according to the condition/requirement.
STU ID NAME SUBJECT
29/074 PADAM NEPAL COMPUTER
10/074 JANAK BHANDARI COMPUTER

Super key in DBMS
• A super key is a set of one or more attributes
(columns), which can uniquely identify a row in a table.
• A Super key for an entity is a set of one or more
attributes whose combined value uniquely identifies
the entity in the entity set.
• A super key is a combine form of Primary Key,
Alternate key and Unique key and Primary Key, Unique
Key and Alternate Key are subset of super key.
• A Super Key is simply a non-minimal Candidate Key,
that is to say one with additional columns not strictly
required to ensure uniqueness of the row. A super key
can have a single column.

example
• Example:
Super Keys in college_Info Table.
• university_Id
• college_Name
• college_Code
• { university_Id, college_Code }
• { college_Name , college_Code }
Super Keys in Student_Information Table:
• Student_Id
• College_Id
• stu_Roll_No
• { Student_Id, Student_Name}
• { College_Id, Branch_Id }
• { stu_Roll_No, Session }

Candidate Key
• A Candidate key is an attribute or set of attributes that uniquely identifies
a record. Among the set of candidate, one candidate key is chosen as
Primary Key. So a table can have multiple candidate key but each table can
have maximum one primary key.
• a candidate key is a minimal super key with no redundant attributes.
Example:
Possible Candidate Keys in college_Info table.
• university_Id
• college_Name
• college_Code
• Possible Candidate keys in Student_Information table.
• Student_Id
• College_Id
• stu_Rollno

Properties of Candidate key
• It must contain unique values
• Candidate key may have multiple attributes
• Must not contain null values
• It should contain minimum fields to ensure
uniqueness
• Uniquely identify each record in a table

How candidate key is different from super
key?
• Candidate keys are selected from the set of
super keys, the only thing we take care while
selecting candidate key is: It should not have
any redundant attribute. That’s the reason
they are also termed as minimal super key.
• all the candidate keys are super keys. This is
because the candidate keys are chosen out of
the super keys.

Primary Key
• A Primary key uniquely identifies each record in a table
and must never be the same for the 2 records.
• Primary key is a set of one or more fields ( columns) of
a table that uniquely identify a record in database
table.
• A table can have only one primary key and one
candidate key can select as a primary key.
• The primary key should be chosen such that its
attributes are never or rarely changed, for example, we
can’t select Student_Id field as a primary key because
in some case Student_Id of student may be changed

Primary key examples
• Primary Key in college_Info table:
• university_Id
• Primary Key in Student_Information Table:
• College_Id

Rules for defining Primary key:
• Two rows can't have the same primary key
value
• It must for every row to have a primary key
value.
• The primary key field cannot be null.
• The value in a primary key column can never
be modified or updated if any foreign key
refers to that primary key.

What is the Alternate key?
• A table can have multiple choices for a primary key but only one
can be set as the primary key. All the keys which are not primary
key are called an Alternate Key.
• Alternate keys are candidate keys that are not selected as primary
key. Alternate key can also work as a primary key. Alternate key is
also called “Secondary Key”.
Example:
Alternate Key in college_Info table:
• college_Name
• college_Code
• Alternate Key in Student_Information table:
• Student_Id
• stu_Roll_No

Unique Key:
• A unique key is a set of one or more attribute that can be used to
uniquely identify the records in table. Unique key is similar to
primary key but unique key field can contain a “Null” value but
primary key doesn’t allow “Null” value. Other difference is that
primary key field contain a clustered index and unique field contain
a non-clustered index.
Example:
Possible Unique Key in college_Info table.
• college_Name
• Possible Unique Key in Student_Information table:
• stu_Roll_No

Composite Key:
• Composite key is a combination of more than one
attributes that can be used to uniquely identity each
record. It is also known as “Compound” key. A composite
key may be a candidate or primary key.
Example:
Composite Key in college_Info table.
• { Branch_Name, Branch_Code}
Composite Key in Student_Information table:
• { Student_Id, Student_Name }

Foreign Keys:
• Foreign key is used to generate the relationship between the tables.
Foreign Key is a field in database table that is Primary key in
another table. A foreign key can accept null and duplicate value.
• The purpose of Foreign keys is to maintain data integrity and allow
navigation between two different instances of an entity. It acts as a
cross-reference between two tables as it references the primary key
of another table.
• Foreign keys are the columns of a table that points to the primary
key of another table.
Example:
university_Id is a Foreign Key in Student_Information table that
primary key exist in college_Info(university_Id) table.

Cont.
• Practically, the foreign key has nothing to do
with the primary key tag of another table, if it
points to a unique column (not necessarily a
primary key) of another table then too, it
would be a foreign key. So, a correct definition
of foreign key would be: Foreign keys are the
columns of a table that points to
the candidate key of another table.

Natural Keys:
A natural key is a key composed of columns that
actually have a logical relationship to other
columns within a table. For example, if we use
Student_Id, Student_Name and Father_Name
columns to form a key then it would be “Natural
Key” because there is definitely a relationship
between these columns and other columns that
exist in table. Natural keys are often called
“Business Key ” or “Domain Key”.

Surrogate Key:
• Surrogate key is an artificial key that is used to
uniquely identify the record in table. For
example, in SQL Server or Sybase database
system contain an artificial key that is known
as “Identity”. Surrogate keys are just simple
sequential number. Surrogate keys are only
used to act as a primary key.

Conclusion
• Database generally only contain Primary Key,
Foreign Key, Unique Key and Surrogate key and
other remaining keys are just concept. A table
must have a unique key. According to Dr. E. F.
Codd ‘s third rule “Every single data element
(value) is guaranteed to be accessible logically
with a combination of table-name, primary-key
(row value), and attribute-name (column value)”.
So each table must have keys , because use of
keys make data highly reliable and provide
several types of content like unique data and null
values.

Types Of Keys in DBMS

Recommended

More Related Content

What's hot (20)

Similar to Types Of Keys in DBMS (20)

More from PadamNepal1 (8)

Recently uploaded (20)

Types Of Keys in DBMS