Please enable Javascript to correctly display the contents on Dot Net Tricks!
 
Coding is Rhyme
Handy Tricks For Beginners & Professionals
SQL Server 2012, 2008 - Tutorial, Article, Archive, Pdf, Handy Tricks, Training, Code Snippets, Reference Manual, T-SQL, PL/SQL, Development, Administration, Performance Tuning, Backup, Recovery, Reporting, Analysis, Security, XML, CLR Objects

Different Types of SQL Keys

Posted By : Shailendra Chauhan, 27 Jan 2011
Updated On : 26 Jun 2014
  Version Support : SQL Server 2005,2008,2012,2014
Keywords : Sql Keys, Super Key, Candidate Key ,Primary Key, Alternate Key, Composite Key, Unique Key, Foreign Key, Types of Keys in Sql Server pdf

A key is a single or combination of multiple fields in a table. Its 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.

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.

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

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

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

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

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

  6. Unique Key

    Uniquekey 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. For more help refer the article Difference between primary key and unique key.

  7. Foreign Key

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

    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.

Defining Keys in SQL Server

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

Note

  1. Practically in database, we have only three types of keys Primary Key, Unique Key and Foreign Key. Other types of keys are only concepts of RDBMS which you should know.

What do you think?

In this article I try to explain types of keys in SQL Server with example. I hope after reading this article you will be aware of keys in SQL Server. I would like to have feedback from my blog readers. Please post your feedback, question, or comments about this article.

About the Author
Shailendra Chauhan works as Sr. Software Engineer at reputed MNC and has more than 5 years of hand over Microsoft .NET technologies. He is a .NET Consultant and is the founder & chief editor of www.dotnet-tricks.com and www.dotnetinterviewtricks.com blogs. He is an author of books ASP.NET MVC Interview Questions and Answers & LINQ Interview Questions and Answers.
He loves to work with web applications and mobile apps using Microsoft technology including ASP.NET, MVC, C#, SQL Server, WCF, Web API, Entity Framework,Cloud Computing, Windows Azure, jQuery, jQuery Mobile, Knockout.js, Angular.js and many more web technologies. More...
 
 
Recommended Articles!
 
Subscribe & follow Us
 
Creative Commons License
Dot Net Tricks is licensed under a Creative Commons Attribution 4.0 International License.
Free .NET Interview Books
 
Learn In Hindi
Browse By Category
 
Recent Articles
 
Popular Articles
 
Like us on Facebook
 
Featured AD