UNIQUE CONSTRAINT

            Unique constraint doesn’t allow inserting duplicate values in specific column(s); it allows only one null value. Multiple Unique constraints we can define on a table.

Applying Unique constraint on a new table:

Syntax:-

CREATE TABLE <Table name>

(<Column 1> <Data type>,

<Column 2> <Data type>,

.

.

.

<Column N> <Data type>,

CONSTRAINT <Constraint name> <Constraint type> (Column 1, Column 2,...,Column N)

)

Example 1: Applying constraint on single column with constraint name.

CREATE TABLE ConstraintTBL_UC1

(

ID INT

,Name VARCHAR(10)

,CONSTRAINT ID_UC UNIQUE(ID)

)

Output: SP_HELP ConstraintTBL_UC1

 

Example 2: Applying constraint on single column with default constraint name.

CREATE TABLE ConstraintTBL_UC2

(

ID INT

,Name VARCHAR(10)

,UNIQUE (ID)

)

Note: If we are not mentioned constraint name, it will set some default name as constraint name.

Output: SP_HELP ConstraintTBL_UC2

 

Example 3: Applying constraint on multiple columns.

CREATE TABLE ConstraintTBL_UC3

(

ID INT

,Name VARCHAR(10)

,CONSTRAINT ID_UC3 UNIQUE(ID,Name)

)

Output: SP_HELP ConstraintTBL_UC3

Apply Unique constraint on existing table:

Note: The specific column(s) should not have any duplicate values and should not have more than one null value then only we can apply unique constraint.

Syntax: 

 ALTER TABLE <Table name> ADD CONSTRAINT <Constraint name> <Constraint type> (<Column 1>, <Column 2>,…,<Column N>)

Example:

ALTER TABLE ConstraintTBL_UC1 ADD CONSTRAINT ID_UC4 UNIQUE (Name)

  

Output: SP_HELP ConstraintTBL_UC1

 

Drop constraint:

Syntax:

ALTER TABLE <Table name> DROP CONSTRAINT <Constraint name>

Example:

ALTER TABLE ConstraintTBL_UC1 DROP CONSTRAINT ID_UC4

Output: