SQL -Structured Query Language - Sql Key & Constraint - Unique Key Tutorial

Unique Key Constraint is used, when we want to keep the value of a specific column unique i.e non-repeatable. After creating a table using a unique key constraint, it doesn’t allow to insert of repeated value.

The syntax for Unique key-

create table user_detail

(

    id int primary key,

    email varchar(50),

    unique(id)

);

OR 

create table user_detail

(

    id int primary key,

    email varchar(50) unique

);

Run Query

In this, we define email with unique key constraints while creating a user_detail table. With a unique key, it does not allow to repeat the same email more than one time.

It will give an error (UNIQUE constraint failed: user_detail.email) while inserting the same email more than one time.

With constraint name-

CONSTRAINT Unique_id UNIQUE (id)

It is best practice to add a constraint name for any constraint. just add CONSTRAINT constraint_name before UNIQUE.

By default constraint name is already created. But you can write your own constraint name.

Syntax For Adding Unique key With Alter Command-

ALTER TABLE user_detail
ADD UNIQUE (id);

With Constraint-

ALTER TABLE user_detail
CONSTRAINT Unique_id 
ADD UNIQUE (id);

This syntax will add a unique key after table creation. If it gives an error make sure that records already have a unique value.

Syntax For Deleting Foreign Key With Alter Command-

ALTER TABLE table_name
DROP INDEX unique_key_constraint_name;

For example- 

ALTER TABLE user_detail
DROP INDEX Unique_id;

This syntax will delete a unique key with its constraint name.