A key refers to an attribute or a set of attributes (a combination of
one or more columns).
It helps us in identifying a row(or tuple) uniquely in a table (or
relation) or in establishing relationships between the different
columns and tables of a relational database.
Any row in the table can be identified by using a key & in a
real-world scenario, it becomes imperative from an efficiency standpoint
to have keys set up in their databases.
The following table is used for examples in providing an
understanding.
In this table, employee details are provided, first is the unique id
then their first & last name with gender is given.
So let's get on with the task at hand and learn about the different
types of keys
Each key has a specific purpose, these are:
1. Primary key
2. Candidate key
3. Super key
4. Foreign key
5. Alternate key
6. Composite key
7. Artificial key
Primary key
Unique identification of a row is a must in a table and for that, there
is the primary key, this key is the most widely known amongst all the
keys and there is always only one of these in a table.
It is to be noted that two rows cant have the same primary key & it cannot be a null value.
It is to be noted that two rows cant have the same primary key & it cannot be a null value.
As it will contradict the earlier stated point of unique identification
of every row, the primary key constraint will not allow this. Thus a
column with unique values must be part of the primary key.
for example in the above-given table Employee ID is the primary
key.
Candidate key
Any set of columns or attributes that are unique and without null
values can be considered a candidate key.
It is like having a set of possible keys to choose from to make the
primary key.
In correlation with the super key, it can be considered as a super key
without any unnecessary columns
Just like a primary key is needed a candidate key is also needed in a
table. for example, the id column is a candidate key and a primary key at
the same time.
Super key
A super key is a group of keys that uniquely identify a row in a
table.
A super key is a superset of candidate keys, thus these keys may have unneeded columns added to them.
For example, in the above example, any combination of columns with the
ID column will be a super key.
Foreign key
With the advent of big data, a need arose for data integrity while
different tables were being interconnected. A foreign key is a key that
interconnects two different tables.
For this, the foreign key is needed as a column in both the tables,
since its purpose is to cross-reference between two tables the foreign
key must be the primary key in the other table.
Alternate key
An alternate key, as the name suggests, is the alternative to the
primary key, it can be considered as one of the keys that comprise the
list of candidate keys and all of the keys that are not the primary key
can be considered as alternate key.
For example, if the given table had a column of distinct roll numbers or
email id then we could have considered those individual columns as a way
to uniquely identify a row in the table, and thus since these columns
would not have been a primary key these would be considered as alternate
keys.
Composite key
When there is a lack of uniquely valued filled columns in a table, this
problem can be generally solved by having different columns combined to
form a key meaning these columns when considered together will create a
column that is uniquely identifiable and can uniquely identify any row of
a table, meaning this composite key will become the primary key.
Artificial key
Sometimes the primary key becomes quite lengthy and complex, due to the
nature and sheer size of big data this scenario is unavoidable, and thus
comes an artificially created key to serving the function of the primary
key as the primary key although qualifies the requirements has no
relations with the table.
Tags:
Data Base