A key is a unique identifier for a row in a database. Keys are necessary for a database because they form the bedrock of relational databases. With keys, rows can easily be picked and separated from others where they can be used for computations.
In a relational database, there are different types of keys. Before we proceed, we explain these types of keys with the help of the table below.
A primary key is a unique key, which will identify only a single row in a table and only that single row alone. A primary key cannot point to multiple records in a table.
Employee table, we simply choose the
id column as our primary key. This will mean that any row that it identifies is a unique entity of its own and cannot mean the same as another row in that table, even if other columns hold the same data.
Looking at the image below, the columns marked in blue can also be chosen as the primary key. That will lead us to the next type of key.
A candidate key is a table column or a set of table columns that can uniquely identify a row in the table. After the primary key of choice has been taken, any other column which can uniquely identify the rows of that table is a candidate key.
mobile_nocan serve as candidates to be used as primary keys. So, a candidate key is a unique identifier that has the potential to serve as a primary key.
A foreign key is a key in one relation (table) which refers to a primary key in another relation. It simply means that this key will be constrained by the primary key column in the reference table.
Normally, a foreign key constraint column cannot be deleted without some actions being initiated. Some of these actions include:
Employee table can have its primary key,
id, as a foreign key in say a
A super key is a set of all unique identifiers that could possibly exist in a table. It consists of all candidate keys, and composite keys as well.
Employee table, the following columns can be a part of the super key set.
The super key superset can contain more values than the ones indicated in the table above. The combination must uniquely identify the rows in the table.
A composite key simply indicates that the key in question was made with the combination of more than one column. Sometimes, you may want to combine more than one column in your database table to uniquely identify a row. Both primary keys and foreign keys can be composite in nature depending on how you wish to identify your rows and the columns available.
There are also alternate keys which are all other unique keys left after the primary key has been selected from the candidate keys set.
View all Courses