Different Types of Keys for Uniqueness in SQL Server:

Different Types of Keys for Uniqueness in SQL Server:

 

In SQL Server we have many keys for uniqueness in table row or value or retrieve records/data-rows from data table.

For example we have two tables in SQL one for User records other one is branch table.

Primary key: User table userid field work as primary key in table. Primary key enforces uniqueness of the column. Primary key create Clustered Index on the column. Primary key does not allow NULL. Only one primary key constraint can be created for each table.

Unique Key: User table usercode field work as unique key in table. Unique key enforces uniqueness of the column. Unique key create NON-Clustered Index on the column. Unique key allows one NULL value.

Candidate Key: User table username, userid and usercode field set called as a Candidate Key. That means a set of one or more fields/columns that can identify a record uniquely in a table called as Candidate key. There can be multiple Candidate Keys in one table. Each Candidate Key can work as Primary Key.

Alternate Key: User table username and usercode two field combination work as alternate key. Basically it is a candidate key that currently is not primary key. Its can work as a primary key in table.

Foreign Key: User table branchid field work as foreign key and branch table same field branchid work as primary key. That means Foreign Key is a field in database table that is Primary key in another table. It can accept multiple null, duplicate values.

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.