SQL Unique Constraint

Overview: In this tutorial you will understand, how you can use SQL UNIQUE constraint and it is use to guaranty the uniqueness of data in a field, column or a group of columns.

Both the UNIQUE and PRIMARY KEY constraints provide a guaranty for uniqueness for a column or set of columns, However UNIQUE will allow to enter NULL values but PRIMARY KEY constraints don’t allow.

And the second difference, you can have many UNIQUE constraints per table, but only one PRIMARY KEY constraint per table.

Syntax for SQL Unique Constraint:

CREATE TABLE Table_name(
             Column_name1 Dataype3,
             Column_name2 Dataype2,
             Column_name3 Dataype3,
             Column_name4 Dataype4 UNIQUE
             );

In above syntax you can see that UNIQUE keyword is used to define Column_name4 as unique.

Lets take few examples to understand it more

Example #1: How to make a column unique in SQL

Suppose you want to create table for customer_info and you want to make customer Email_ID as UNIQUE for table, So here we care going to make A single column as unique.

CREATE TABLE Customer_info (
             Customer_ID int NOT NULL Primary key,
             Customer_Name varchar(55),
             Email_ID varchar(50) UNIQUE,
             );

In above example you can see that the CREATE TABLE command created a customer_info table where Email_ID field is unique. It means this field or column cannot be replicate or one customer cannot have same email ID.

Example #2: How to make group of column UNIQUE

Lets first understand the Syntax for applying UNIQUE on group of columns or more then one columns in one go. In below Syntax you can say that this query making Column1 and Column 2 as UNIQUE constraint.

CREATE TABLE table_name (
             column_name_1 data_type1,
             column_name_2 data_type2,
             column_name_3 data_type3,
             ......,
             UNIQUE (column_name_1,column_name_2)
);

With Example for customer_info table, lest take example with again create table for customer_info with query for UNIQUE on multiple columns

So in below example we have created Email_ID, Customer_Login_ID two column as UNIQUE for customer_info table.

CREATE TABLE customer_info (              
             Customer_ID int NOT NULL Primary key,
             Customer_Name varchar(55),
             Email_ID varchar(50),
             Customer_Login_ID varchar(10),
             UNIQUE (Email_ID, Customer_Login_ID)
             );

Example #3: Add UNIQUE Constraint for existing Table in SQL

Lets try to add UNIQUE constraints for existing table which do not have any UNIQUE constraint so far.

Let assume that customer_info table donot have any UNIQUE constraint on Email_ID column, it measn you can enter the duplicate values to Email_ID column and you want to change this columns as UNIQUE, then use below command

ALTER TABLE customer_info 

ADD CONSTRAINT unique_emailID UNIQUE(Email_ID);

So the above command will change the Email_ID column into UNIQUE column, here we have use UNIQUE constraint name as “unique_emailID” for SQL to identify. As this name will help in future to delete the QNIQUE constraint

Example #4: how to remove unique constraint in SQL

If you want to delete UNIQUE constraint from SQL table the you need to use DROP command in SQL for deleting the UNIQUE constraint and also need to specify the UNIQUE constraint name

Use the ALTER TABLE DROP CONSTRAINT statement as follows

Syntax:

ALTER TABLE table_name

DROP CONSTRAINT constraint_name;

Lets drop Email_ID from customer_infor tabel which we have created on above

ALTER TABLE customer_info 

DROP CONSTRAINT unique_emailID ;

Conclusion:

A UNIQUE key in SQL is a keyword which is used to set of fields or columns to make records unique. UNIQUE constraint can be apply for one column or more then one column in SQL table. it can be alter, and drop also.