In this article describe the concept of keys in sql server. Keys has play the important role in the database. Here many important keys are describe like: primary key, foreign key, unique key, candidate key with simple examples.

A key is a single or combination of multiple fields in a table. It is used to fetch or retrieve records/data-rows from data table according to the condition/requirement. Keys are also used to create relationship among different database tables or views.

Some important keys we describe here,

·         Primary key

·         Foreign key

·         Unique key

·         Candidate key 

Primary Key 

The primary key are those key which are uniquely identifies each record in a database table. A primary key is used to uniquely identify each row in a table. Primary keys must contain unique values. A primary key column cannot contain NULL values. Each table should have a primary key, and each table can have only ONE primary key. If a table created and accidentally you did not give the primary key field then one of the fields is typically assigned as the primary key. While the primary key is often a number, it may also be a text field or other data type.

Example 

Here we describe three methods to create the foreign key.

1)

CREATE TABLE Employee(  // Employee is a table name
empID VARCHAR(10) PRIMARY KEY // here we make the primary key, 
empFirstName VARCHAR(20), 
empLastName VARCHAR(20), 
empAddress VARCHAR(20) 
);

2)

CREATE TABLE Employee (   
      
empID VARCHAR(10),
 
empFirstName VARCHAR(20),
 
empLastName VARCHAR(20),
 
empAddress VARCHAR(20)
 
PRIMARY KEY(empID) // here we make the primary key
 
);

3)  

CREATE TABLE Employee (   
      
empID VARCHAR(10),
 
empFirstName VARCHAR(20),
 
empLastName VARCHAR(20),
 
empAddress VARCHAR(20)
 
CONSTRAINT pk_Employee PRIMARY KEY(empID) // here we make the primary key
 
);

Here we feed records with same employee id which is primary key thus it error

because cannot insert duplicate key in primary field.

Here we give blank in primary key field which give error because cannot insert null

value primary field.

 Foreign key

A foreign key in one table points to a primary key in another table. When we want to implement relationship between two tables then we use concept of foreign key. It is also known as referential integrity. We can create more than one foreign key per table.

This figure to demonstrate the foreign key with two tables;

Example

Here we describe three methods to create the foreign key.

1)

  CREATE TABLE Salary (
 
salID VARCHAR(10) NOT NULL PRIMARY KEY,
 
salAmmount int NOT NULL,
 
empID VARCHAR(10) references Employee(empID) // here we make the foreign key
 
 
);

2) 

 CREATE TABLE Salary (
 
salID VARCHAR(10) NOT NULL PRIMARY KEY,
 
salAmmount int NOT NULL,
 
empID VARCHAR(10)
 
FOREIGN KEY (empID) REFERENCES Employee(empID) // here we make the foreign key
);

3) 

 CREATE TABLE Salary (
 
salID VARCHAR(10) NOT NULL PRIMARY KEY,
 
salAmmount int NOT NULL,
 
empID VARCHAR(10)
 
CONSTRAINT fk_PerOrders FOREIGN KEY (empID)
REFERENCES Employee(empID)                      // here we make the foreign key
);

Unique key

The unique key uniquely identifies each record in a database table that means each record of row which is uniquely identifies that do not accept duplicate value. Although both a unique key and a primary key enforce uniqueness, use a unique key instead of a primary key when you want to enforce the uniqueness of a column, or combination of columns, that is not the primary key. Multiple unique key can be defined on a table, whereas only one primary key can be defined on a table. 

Note: A unique key field can be inserted null value accept but in case of primary key cannot be inserted null value. 

Example

Here we describe three methods to create the unique key.

1) 

 CREATE TABLE CUSTOMER (
 
custID INT PRIMARY KEY,
 
custName VARCHAR(30) NOT NULL,
 
custAddress VARCHAR(50) NOT NULL,
 
custMobile VARCHAR(13) UNIQUE, // create the unique key
 
custPincode VARCHAR(10)
 
);

2)  

CREATE TABLE CUSTOMER (
custID INT PRIMARY KEY,
custName VARCHAR(30) NOT NULL,
custAddress VARCHAR(50) NOT NULL,
custMobile VARCHAR(10),
custEmail Varchar(50) NOT NULL,
custPincode VARCHAR(10)
UNIQUE(custMobile), UNIQUE(custEmail) // create more than one unique key
);

3)  

CREATE TABLE CUSTOMER (
custID INT PRIMARY KEY,
custName VARCHAR(30) NOT NULL,
custAddress VARCHAR(50) NOT NULL,
custMobile VARCHAR(10), 
custPincode VARCHAR(10)
CONSTRAINT uc_Customer UNIQUE (custMobile) // create the unique key
);

Candidate key

A Candidate Key can be any column or a combination of columns that can qualify as unique key in database that means a column or Combination of columns which can help uniquely identify a record in a table without the need of any external data is called candidate key. There can be multiple Candidate Keys in one table. Each Candidate Key can qualify as Primary Key.

Example

CREATE TABLE PRODUCT (
 
ProductID int PRIMARY KEY,
 
ProductName VARCHAR(50) NOT NULL UNIQUE,
 
ProductNumber VARCHAR(20) NOT NULL UNIQUE,
 
ProductDescription VARCHAR(50)
 
);