24. Different types of SQL keys?

Types of SQL Keys:

We have following types of keys in SQL which are used to fetch records from tables and to make relationship among tables or views.

Super Key

Super key is a set of one or more than one keys that can be used to identify a record uniquely in a table. Example: Primary key, Unique key, Alternate key are subset of Super Keys.

Candidate Key

A Candidate Key is a set of one or more fields/columns that can identify a record uniquely in a table. There can be multiple Candidate Keys in one table. Each Candidate Key can work as Primary Key.

Example: In below diagram ID, RollNo and EnrollNo are Candidate Keys since all these three fields can be work as Primary Key.

Primary Key

Primary key is a set of one or more fields/columns of a table that uniquely identify a record in database table. It can not accept null, duplicate values. Only one Candidate Key can be Primary Key.

Alternate key

A Alternate key is a key that can be work as a primary key. Basically it is a candidate key that currently is not primary key.

Example: In below diagram RollNo and EnrollNo becomes Alternate Keys when we define ID as Primary Key.

Composite/Compound Key

Composite Key is a combination of more than one fields/columns of a table. It can be a Candidate key, Primary key.

Unique Key

Unique key is a set of one or more fields/columns of a table that uniquely identify a record in database table. It is like Primary key but it can accept only one null value and it can not have duplicate values. 

Foreign Key

Foreign Key is a field in database table that is Primary key in another table. It can accept multiple null, duplicate values. 

Example: We can have a DeptID column in the Employee table which is pointing to DeptID column in a department table where it a primary key.

Defined Keys-

CREATE TABLE Department
(
DeptID int PRIMARY KEY,
Name varchar (50) NOT NULL,
Address varchar (200) NOT NULL, )
CREATE TABLE Student
(
ID int PRIMARY KEY,
RollNo varchar(10) NOT NULL,
Name varchar(50) NOT NULL,
EnrollNo varchar(50) UNIQUE,
Address varchar(200) NOT NULL,
DeptID int FOREIGN KEY REFERENCES Department(DeptID)
)