Define Super key, Candidate key, Primary key and Foreign key / Type of keys in database / Purpose of Super key, Candidate key, Primary key and Foreign key in database design / Super key, Candidate key, Primary key and Foreign key Examples / How does a Foreign key ensure consistency?
Super key, Candidate key, and Primary key - Definitions and Discussions
Emp_ID
|
Emp_Name
|
DOB
|
Gender
|
Dept_No
|
E101
|
Ramkumar
|
15-JUL-1986
|
M
|
2
|
E103
|
Ramesh
|
04-MAY-1989
|
M
|
1
|
E104
|
Stephen
|
29-OCT-1987
|
M
|
1
|
E102
|
Nirmal
|
23-JAN-1980
|
M
|
3
|
E105
|
Laxmi
|
20-MAY-1988
|
F
|
4
|
E107
|
Rani
|
23-JAN-1980
|
F
|
4
|
E106
|
Ramesh
|
12-MAR-1979
|
M
|
2
|
Table 1 - Employee
Keys
The ability to locate data uniquely is one of the main properties which
must be included while designing any databases. That is, using any single or
set of attribute values one must be able to locate data in the database
uniquely. The attribute(s) is called as key for a relation (table).
To retrieve unique(single) record (entity) from table Employee, we can write the following SQL
query;
SELECT * FROM employee WHERE emp_id = ‘E102’;
This query can get you information about employee ‘Nirmal’, because he
is the only person with the Emp_ID value ‘E102’. And, if you look at the sample
data stored, it is very evident that no two employees have same Emp_ID. Hence,
Emp_ID attribute (column) is one of the keys for the table Employee.
Types of Keys
1. Superkey
Set of one or more attributes together can uniquely identify rest of
the attributes of a relation uniquely is called Superkey.
In our example, Emp_ID can uniquely identify any records in Employee.
Likewise, any of the combinations, (Emp_ID, Emp_Name), (Emp_ID, DOB), (Emp_ID,
Gender), (Emp_ID, Dept_No), (Emp_Name, DOB), (Emp_Name, DOB, Gender), and so on
[you form the other combinations which can uniquely identify records] can also
identify the records uniquely.
For any table, we have at least one super key which is the combination
of all the attributes of the table. This is trivial superkey. In RDBMS, no two
records are allowed to be the same. For example, at least the complete single
record (E101, Ramkumar, 15-JUL-1986,
M, 2) is different from (E105, Laxmi, 20-MAY-1988, F, 4). Hence, we have at
least one super key.
Note: Redundant attributes are permitted in Super Keys. In (Emp_ID,
Emp_Name), Emp_Name is unnecessary and redundant.
2. Candidate Key
Minimal Super key is a key which is a super key without any redundant
attribute (unnecessary combination). In other words, if you remove any
attribute from a key combination, it should not be able to uniquely identify
data. That is we need a minimal combination of attributes. For example,
(Emp_ID, Emp_Name) is a super key with an unnecessary attribute Emp_Name. Here,
without Emp_Name attribute, only Emp_ID attribute can uniquely identify
records. Hence, Emp_ID is itself a minimal super key.
Candidate key is such a minimal super key with another condition as
follows;
Minimal super key with no
component in its proper subset is a super key.
[Proper subset – if A is subset of B and A is not equal to B then A is
the proper subset of B]
For example, {(Emp_ID), (Emp_Name)} is proper subset of set (Emp_ID,
Emp_Name). In this, (Emp_ID) is itself a super key. Hence, (Emp_ID, Emp_Name)
is not a candidate key but a super key.
As another example, (Emp_Name, DOB) is a super key and it is minimal.
That is, the proper subset {(Emp_Name), (DOB)} does not contain any super keys.
So, (Emp_Name, DOB) is a candidate key.
For our table Employee’s instance (information stored at a particular
moment in a table is called instance), we have the following candidate keys
(please check them);
(Emp_ID)
(Emp_Name, DOB)
(Emp_Name, Dept_No)
(DOB, Dept_No)
(DOB, Gender)
All the listed candidate keys are minimal. If you divide them further,
they lose the property of a key. That is, for example, Dept_No alone cannot
uniquely identify.
Why do we need to identify set of candidate keys?
They are helping in normalizing a table. That is, they are helping in
eliminating unwanted anomalies. More on Boyce Codd Normal Form (BCNF).
3. Primary Key
Any relation (table) in Relational Database Management Systems (RDBMS)
must have a primary key, which is one of the candidate keys which are minimal.
That is, we choose one key among the list of candidate keys as primary key for
a table. While choosing the primary key we are looking for simple candidate key
among all the candidate keys. For example, in the above list of candidate keys,
we choose (Emp_ID) as primary key because it is simple and single attribute
key.
What is the purpose of Primary Key?
As I said earlier, it helps us in identifying data uniquely in the
database. Also, it helps in normalizing your table and helps in performance
boosting especially when you access your data using primary key.
4. Foreign Key
Foreign key is an attribute (or set of attributes) of one table
(relation) which refers its value from another table’s Primary key (preferably)
to establish a connection between them. In other words, it can be stated as “It
is a constraint which helps in maintaining consistency on an attribute (or set
of attributes) of two tables”.
Example of Foreign Key
Consider the following two relational schemas (Primary keys
underlined).
STUDENT (Regno,
SName, Program, DOJ, DOB)
STU_PHONE (Regno,
Phone)
Here, Regno is the key for
STUDENT and (Regno, Phone) is the key
for STU_PHONE. Construction of separate table for storing Phone numbers is due
to the intent to store multiple phone numbers for single student. That is, this
design enables us to store zero or more phone numbers. It is clear that, if we
would like to store any phone numbers, the condition is that the phone number
must belong to some student. In other words, if you like to enter any phone
number, then there must be a student registered for any program. Based on this
condition, we designate Regno of STU_PHONE as the Foreign Key which refers
the value of any existing STUDENT records’ Regno.
How does a Foreign Key ensure consistency?
Foreign key shows Parent-Child relationship. That is, there must be a parent (tuples
in STUDENT) record for every child record (tuples in STU_PHONE). Hence, it acts
as a condition.
And, it ensures consistency through cross verifying the values entered
in the child table against the values stored in the parent table, thereby gives
security from mistyping some values.
In its implementation of Foreign Key, if anybody entering a record in
STU_PHONE, the Regno column value
will be verified with the Regno
column of STUDENT table for the existence of such values.
For example, if you like to enter a record (‘12MKV001’, 9897909411) in
STU_PHONE, then you must have a record in STUDENT with Regno value ‘12MKV001’
Conclusion:
A table can have at most one Primary key.
Every Primary key is one of the candidate keys.
Every Primary key is a super key. Not all the super keys are primary
key.
All the candidate keys except Primary key are called Alternate keys.
Primary key which consists of more than one attribute is called Composite key.
In any table, only one Primary key is permitted.
Foreign keys need not always refer to primary keys. it needs some
attributes which are UNIQUE.