Different Types of Database Keys
Candidate Key
Primary Key
Super Key
Alternate Key
Foreign Key
Composite Key
1
Keys
Keys are one of the basic requirements of a relational
database model.
It is widely used to identify the tuples(rows) uniquely in
the table.
We also use keys to set up relations amongst various
columns and tables of a relational database.
2
Candidate Key
A candidate key refers to a set of attributes that can uniquely
identify each record in a table. For Example, STUD_NO in
STUDENT relation.
It is a super key with no repeated data is called a candidate key.
It must contain unique values.
3
Candidate Key
Every table must have at least a single candidate key.
A table can have multiple candidate keys but only one primary key.
The value of the Candidate Key is unique and may be null for a
tuple.
There can be more than one candidate key in a relationship.
4
Candidate key
Example:
STUD_NO is the candidate key for relation STUDENT.
Table STUDENT
STUD_NO SNAME ADDRESS PHONE
1 Shyam Delhi 123456789
2 Rakesh Kolkata 223365796
3 Suraj Delhi 175468965
The candidate key can be simple (having only one attribute)
or composite as well
.
5
Example:
{STUD_NO,COURSE_NO}is a composite candidate key for
relation STUDENT_COURSE.
Table STUDENT_COURSE
Note: unique constraint that has a null able column, allows the
value ‘ null ‘ in that column only once .
That’s why the STUD_PHONE attribute is a candidate here,
but can not be a ‘null’ value in the primary key attribute.
STUD_NO TEACHER_NO COURSE_NO
1 001 C001
2 056 C005
6
Primary Key
Which is Unique & Can’ t be have NULL Value
Primary key Is the column you choose to maintain uniqueness in a
table at row level.
For Example, STUD_NO, as well as STUD_PHONE, are candidate
keys for relation STUDENT but STUD_NO can be chosen as the
primary key (only one out of many candidate keys).
It is a unique key.
It can identify only one tuple (a record) at a time.
It has no duplicate values, it has unique values.
It cannot be NULL.
Primary keys are not necessarily to be a single column; more than
one column can also be a primary key for a table.
7
Primary Key
Example:
STUDENT table -> Student(STUD_NO, SNAME, ADDRESS,
PHONE) , STUD_NO is a primary key
Table STUDENT
STUD_NO SNAME ADDRESS PHONE
1 Shyam Delhi 123456789
2 Rakesh Kolkata 223365796
3 Suraj Delhi 175468965
8
Super Key
The set of attributes that can uniquely identify a tuple is known as
Super Key. For Example, STUD_NO, (STUD_NO,
STUD_NAME), etc. A super key is a group of single or multiple
keys that identifies rows in a table.
It supports NULL values.
A candidate key is a super key but vice versa is not true.
Super Key values may also be NULL.
Example:
Consider the table shown above.
STUD_NO+PHONE is a super key.
9
Super Key
Example:
Consider the table shown above.
STUD_NO+PHONE is a super key.
10
Alternate Key
All the keys which are not primary keys are called alternate keys.
It is a secondary key.
It contains two or more fields to identify two or more records.
These values are repeated.
E.g.:- SNAME, and ADDRESS is Alternate keys
11
12
Foreign Key
If an attribute can only take the values which are present
as values of some other attribute, it will be a foreign key
to the attribute to which it refers.
The referenced attribute of the referenced relation should
be the primary key to it.
13
Foreign Key
It is a key it acts as a primary key in one table and it acts
as secondary key in another table.
It combines two or more relations (tables) at a time.
14
Foreign Key
Example:
STUD_NO in STUDENT_COURSE is a foreign keys to
STUD_NO in STUDENT relation.
Table STUDENT_COURSE
STUD_NO TEACHER_NO COURSE_NO
1 005 C001
2 056 C005
15
Foreign Key
It need not follow uniqueness constraint. For Example, STUD_NO
in the STUDENT_COURSE relation is not unique. It has been
repeated for the first and third tuples. However, the STUD_NO in
STUDENT relation is a primary key and it needs to be always
unique, and it cannot be null.
16
Composite Key
Sometimes, a table might not have a single column/attribute that
uniquely identifies all the records of a table.
To uniquely identify rows of a table, a combination of two or more
columns/attributes can be used.
It acts as a primary key if there is no primary key in a table
Two or more attributes are used together to make a composite key.
Example:
FULLNAME + DOB can be combined
together to access the details of a student.
17
18
Any Questions
&
Suggestions
24