Introduction:

Here we will explain how to use unique key in SQL Server or what is unique key in SQL Server with example.

Description:

A unique key in SQL Server is uniquely identify a record in a database table. It can have one or more than one fields/columns in a table. It’s 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 field/column or a set of fields/columns.

There is an automatically defined unique key constraint within a primary key constraint.

There may be more than one unique key constraint 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 Emp_ID column when the employee table is created, use the following SQL syntax:

create table employee
(
Emp_ID bigint unique,
Emp_FirstName varchar (255),
Emp_LastName varchar (255),
Emp_City varchar (255),
Emp_Email varchar(200) 
)

 

Defining a unique key constraint on multiple columns:

If you want to create UNIQUE constraint more than one columns when the employee table is created, use the following SQL syntax:

 

create table employee
(
Emp_ID bigint unique,
Emp_FirstName varchar (255),
Emp_LastName varchar (255),
Emp_City varchar (255),
Emp_Email varchar(200),
CONSTRAINT UC_Emp_ID UNIQUE (Emp_ID, Emp_Email)   
)

SQL UNIQUE KEY constraint on ALTER TABLE:

If you want to create a unique constraint on Emp_ID column when the table is already created, you should use the following SQL syntax:

Defining a unique key constraint on single column:

ALTER TABLE employee 
ADD UNIQUE (Emp_ID)

Defining a unique key constraint on multiple columns:

ALTER TABLE employee 
ADD CONSTRAINT UC_Emp_ID UNIQUE (Emp_ID, Emp_Email)

DROP SYNTAX FOR A FOREIGN KEY constraint:

If you want to drop a UNIQUE constraint, use the following SQL syntax:

ALTER TABLE employee 
DROP CONSTRAINT UC_Emp_ID

Thanks.

0 comments


Leave a comment

Make sure you enter the (*) required information where indicated. HTML code is not allowed.

Welcome to SQL Tutorials Point

Don't have an account? Click here to Create a new account, it takes less than a minute.