Types of SQL Keys
A key is a single or combination of multiple fields in a table. Its is used to retrieve records or rows from data table according to the condition/requirement. Keys are also used to create relationship among different database tables or views. Below are different SQL Keys.
- Super Key
- Candidate Key
- Primary Key
- Alternate key
- Composite/Compound Key
- Unique Key
- Foreign 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.
A Candidate Key is a set of one or more 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 a student table, ID, RollNo and EnrollNo are Candidate Keys since all these three fields can be work as 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 is the key that can be work as a primary key. Basically it is a candidate key that currently is not primary 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 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 is a field in database table that is Primary key in another table. It can accept multiple null, duplicate values.
Defining SQL Keys
CREATE TABLE Dept
DeptID int PRIMARY KEY, --primary key
DName varchar (50) NOT NULL,
DAddress varchar (200) NOT NULL
CREATE TABLE Student_Master
SID int PRIMARY KEY, --primary key
RollNo varchar(10) NOT NULL,
SName varchar(50) NOT NULL,
EnrollNo varchar(50) UNIQUE, --unique key
Address varchar(200) NOT NULL,
DeptID int FOREIGN KEY REFERENCES Department(DeptID) --foreign key
For More CREATE Table In SQL