Unique and Alternate Keys in SQL

An introduction to Database

UNIQUE KEY IN SQL:

A unique key is a set of one or more than one fields/columns of a table that uniquely identify a record in a database table. Whereas candidate keys other than Primary key are Alternate Keys. You can say that it is little like primary key but it can accept only one null value and it cannot have duplicate values. The unique key and primary key both provide a guarantee for uniqueness for a column or a set of columns. There is an automatically defined unique key constraint within a primary key constraint. There may be many unique key constraints for one table, but only one PRIMARY KEY constraint for one table.

SQL UNIQUE KEY constraint on CREATE TABLE:

If you want to create a UNIQUE constraint on the ?S_Id? column when the ?students? table is created, use the following SQL syntax:

SQL Server / Oracle / MS Access:
(Defining a unique key constraint on single column):

CREATE TABLE students  
(  
S_Id int NOT NULL UNIQUE,  
LastName varchar (255) NOT NULL,  
FirstName varchar (255),  
City varchar (255)  
)  

MySQL:


CREATE TABLE students  
CREATE TABLE students  
(  
S_Id int NOT NULL,  
LastName varchar (255) NOT NULL,  
FirstName varchar (255),  
City varchar (255),  
UNIQUE (S_Id)  
)  

ALTERNATE KEY IN SQL:

Alternate key is a secondary key it can be simple to understand by an example: Let’s take an example of student it can contain NAME, ROLL NO., ID and CLASS, here ROLL NO. is primary key and rest of all columns like NAME, ID and CLASS are alternate key.

If a table has more than one candidate key, one of them will become the primary key and rest of all are called alternate keys. In simple words, you can say that any of the candidate key which is not part of primary key is called an alternate key. So when we talk about alternate key, the column may not be primary key but still it is a unique key in the column.
“An alternate key is just a candidate key that has not been selected as the primary key.”