Key in SQL can be defined as the subset of the columns present in tables which permit to uniquely identify a row. Every row in a table will have unique value for key. Keys are used to fetch records from a table according to the given condition. The different types of key used to retrieve records from table are:
- Superkey
- Candidate Key
- Primary Key
- Alternate Key
- Composite Key
- Foreign Key
Superkey
Superkey is a set of keys which are used to identify a row present in a table. Superkey is the most commonly used key in SQL. For example: consider a table which contains the details of an employee. The table may contain various fields like employee id, name, department, father’s name and birth date. The only unique field present in the table mentioned above is employee id because name, birth date, department, father’s name could be similar to some other employees. Any field like name, birth date can be combined with employee id to declare itself as a superkey.
- Read: More Interview Questions
Candidate Key
Candidate Key can be defined as a super key with no extra attribute. Superkeys always have an extra field like { employee id, name }. Candidate key can be used to identify a unique record in a table. Candidate keys are used only when no other information about other fields like name and birthdate are available. A candidate key is always considered as a superkey but the vice versa is not allowed.
Primary Key
A database may contain numerous super and candidate keys but to identify a record only one particular key is used. The key used to identify the record is called as primary key. If only one candidate key is present in a database, it will be automatically selected as primary key. For example: there are several attributes for employee like employee id, name and department. Out of these attributes only employee id can be used as a primary key while can’t be used.
Points to remember while using primary key
- There will be only one primary key in a relation.
- Values used in primary key must be unique.
- Null values cannot be used as primary key.
Alternate Key
Candidate Keys which are not used as primary key can be classified into alternate key. Consider a database which contains details of students. The student table will contain fields like Roll no, Registration no, name and class. Out of these fields both roll no and registration no can be referred to as primary key. If roll no is referred as a primary key then registration no is called as alternate key.
Composite Key.
If a primary contains more than one attribute then it is referred as a composite key. For example if both registration no and roll no of a student is used to search the records then the particular combination of registration no and roll no is called as composite key. Composite keys are also known as compound keys.
- Do you Know: SQL Clustered & Non-clustered Index
Foreign Key
Foreign key can be defined as a set of attributes which relate towards primary key. The attribute relation between primary and foreign key is always established in a separate table. The table where the relation is established is called as parent table. The key which connects the parent table and primary key can be defined as foreign key.
Do keys used in SQL have NULL value?
The SQL standard does not allow the keys to be declared as NULL. The keys which have columns more necessary are called super key. The keys which have minimum columns are called as minimal superkeys. The presence of at least one “minimal superkey” is imperious for a table. Popular database implementations like Oracle allow the value of foreign and unique key to be NULL. However “primary keys” are not allowed to be declared as NULL.