Menu Close

Different Types of SQL Keys: Example and Uses

In this article we learn about Different Types of SQL Keys: Example and Uses. SQL Server key is an attribute or set of attributes in a relation that uniquely identifies a tuple in a relation. Please read my previous article How to read data from XML string in SQL Server.

Keys are also used to create a relationship among different database tables or views.

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 a 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.

Primary Key

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

Alternate key

An Alternate key is a key that can be work as a primary key. Basically, it is a candidate key that currently is not a 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

A unique key is a set of one or more fields/columns of a table that uniquely identify a record in a database table. It is like Primary key but it can accept only one null value and it can not have duplicate values. For more help refer to the article Difference between primary key and unique key.

Foreign Key

Foreign Key is a field in a database table that is Primary key in another table. It can accept multiple null, duplicate values. For more help refer to the article Difference between primary key and foreign key.

Example

CREATE TABLE DEPARTMENT 
(
 DEPTID       INT PRIMARY KEY, --PRIMARY KEY
 DEPTNAME     VARCHAR (50) NOT NULL,
 ADDRESS      VARCHAR (200) NOT NULL
 ) 
CREATE TABLE EMPLOYEE 
(
 EMPID             INT PRIMARY KEY,              --PRIMARY KEY
 EMPREGNO          VARCHAR(10) NOT NULL,
 EMPNAME           VARCHAR(50) NOT NULL,
 EMPACCESSTOKENNO  VARCHAR(50) UNIQUE,      --UNIQUE KEY
 EMPADDRESS        VARCHAR(200) NOT NULL,
 EMPDEPTID         INT FOREIGN KEY REFERENCES DEPARTMENT(DEPTID) --FOREIGN KEY
) 

The keys connection between two tables like below:

sql-keys

Description about the Key Connection:

In employee table “EMPID” is the primary key whereas in Department table “DeptID” is the primary key.

“DeptID” in both employee and Department table are made Foreign key relationship.

“EmpRegNo” and “EmpAccessToken” are the Alternate keys as both are eligible for primary key.

 “EMPID”,”EmpRegNo” and “EmpAccessToken” are Possible Candidate keys.

Conclusion

So far this article we learnt about Different Types of SQL Keys: Example and Uses.

Leave a Reply

Your email address will not be published.