SQL Keys play a very important role in database related task in SQL like retrieve data/record from the table according to the requirement.  A key can be defined as a single or combination of multiple fields/columns in a table. With the help of keys we not only can retrieve data but also used to create relationship among different database tables. So, in this blog we will discuss about the concept and types of keys used in SQL SERVER

There are different types of keys we can use in SQL SERVER which are as follows:-

    •    Super Key
    •    Candidate Key
    •    Primary Key
    •    Alternate key
    •    Composite/Compound Key
    •    Unique Key
    •    Foreign Key

Now one by one we will discuss theses different types of keys used in SQL SERVER.

1. Super Key

Super key can be defined as a set of one or more than one keys that can be used to identify a record/data uniquely in a table. This key includes only those fields which includes unique value as if we take an example of Employee than Employee_Id will be the field which includes unique value and it become easy to identify the employee from Employee_Id field.

For Example : -Keys which can be the subset of Super Key are Primary key, Unique key and Alternate key. As right now we don’t know about these keys so further we will discuss these keys.

2.  Primary Key

Primary key can be defined as a set of one or more fields/columns of a table that uniquely identify a record in database table. Record can be uniquely identify when the column which includes unique value like Employee_Id of employee from an organization. It will not accept null values  and duplicate values. Only one primary key can be exist  in a single table not more than one.

For Example:- Suppose a table consist of Employee data with fields Employee_Name, Employee_Address,Employee_Id and Employee_Designation so in this table only one field is there which is used to uniquely identify detail of Employee that is Employee_Id.
  

3. Unique Key

Unique key can be defined as a set of one or more fields/columns of a table that have the capability to uniquely identify a record in database table. We can have other fields also in a table beyond primary key which are also able to uniquely identify the record. It  can accept only one null value and it can not have duplicate values in it.

For Example:-Suppose a table consist of Employee data with fields Employee_Name, Employee_Address,Employee_Id , Employee_Designation and Employee_PhoneNo so in this table except Employee_Id we also have an another field named Employee_PhoneNo which is can also be used to uniquely identify the record and can termed as Unique Key.

4. Alternate key

Alternate key can be defined as a key that can be work as a primary key if required. We can also understand this key as a candidate for primary key as candidate key but right now it is not a primary key.

For Example:- Suppose a table consist of Employee data with fields Employee_Name, Employee_Address,Employee_Id , Employee_Designation and Employee_PhoneNo in this case Employee_PhoneNo can be the alternate key as it is also suitable to identify the record uniquely but right now it is not primary key.

5. Candidate Key

Candidate Key can be defined as a set of one or more fields/columns that can identify a record uniquely in a table like primary key or we can also say that other fields than primary key which can become primary key and a table can have more than one candidate key. Each candidate key can work as primary key if required in any case.

For Example:- Suppose a table consist of Employee data with fields Employee_Name, Employee_Address,Employee_Id , Employee_Designation.Employee_PANNo and Employee_PhoneNo  in this table Employee_PhoneNo and Employee_PANNo are Candidate Keys as these two fields can also work as candidate key.

6. Composite/Compound Key

Composite Key can be defined as a combination of more than one fields/columns of a table to uniquely identify the record. Fields which can be combine to make composite key can be candidate,  primary key.

For Example:-Suppose a table consist of Employee data with fields Employee_Name, Employee_Address,Employee_Id , Employee_Designation, Employee_PANNo and Employee_PhoneNo in this table to build a composite key we combine Employee_Id and Employee_PhoneNo to fetch data from table.

7. Foreign Key

Foreign Key can be defined as a field/column in the Company table that is Primary key in Employee table. It can also accept multiple null values and duplicate values. This can be easily understand with the help of example given below.

For Example:- We can have a Employee_Id column in the Company table which is pointing to Employee_Id column in a Employee table where it a primary key. So with the help of foreign key we can easily identify the data from tables.

 Employee Table

      CREATE TABLE Employee
     (
          Employee_Id int PRIMARY KEY, --primary key
          Employee_Name varchar (50) NOT NULL,
          Employee_Address varchar (200) NOT NULL,
          Employee_Designation varchar (50) NOT NULL,
          Employee_PANNo varchar (50) NOT NULL,
          Employee_PhoneNo varchar (50) NOT NULL
      )

  Company Table

     CREATE TABLE Company
     (
        Company_ID int PRIMARY KEY, --primary key
        Company_Name varchar(50) NOT NULL,
        Company_RegisterNo varchar(50) UNIQUE, --unique key
        Company_Address varchar(200) NOT NULL,
        Employee_ID int FOREIGN KEY REFERENCES Employee(Employee_Id) --foreign key
     )

This is the concept of different keys used in SQL SERVER.