What are SQL Unique Constraint with example?

SQL Unique Constraint :

In my previous article i have given idea of different types of constraints in SQL as well as SQL Not null Constraint with its examples.  In This article i will try to explain about other SQL Constraint which is most important constraint of SQL which is specifically used to define the relationship between tables.SQL Unique constraint defines the unique values for that specific column.

The SQL UNIQUE Constraint uniquely identifies each record in a database table.

How To create Unique key Constraint in SQL ?

This section gives you the practical example of creating unique key which is used in real life industry scenarios.

The UNIQUE and PRIMARY KEY Constraints both provide a guarantee for Uniqueness for a column or set of columns.

A PRIMARY KEY Constraint automatically has a UNIQUE Constraint defined on it.

You can have many UNIQUE Constraints per table, but only one PRIMARY KEY Constraint per table is allowed.

Real life Scenario:

CREATE TABLE Student

(

RollNo Number (10) UNIQUE,

FName Varchar2 (15),

LName Varchar2 (15),

Location Varchar2 (20)

);

Try Entering below Query twice:

 Example:

 INSERT INTO Student (RollNo, FName, LName, Location)

Values (101,’Rajiv’, ‘M’, ‘Pune’);

ERROR at line 1:

ORA-00001: unique constraint (Rajiv.SYS_C004784) violated

To find System Constraint with Constraint Type:

SELECT Table_Name, Constraint_Type, Constraint_Name FROM User_Constraints;

To allow naming of a UNIQUE constraint, and for defining a UNIQUE constraint on multiple columns, use the following

SQL Syntax:

CREATE TABLE Student

(

RollNo Number (10),

FName Varchar2 (15),

LName Varchar2 (15),

Location Varchar2 (20),

CONSTRAINT U_Student_FName_LName UNIQUE (FName, LName)

);

Try Entering below Query twice:

 Example:

 INSERT INTO Student(RollNo, FName, LName, Location)

Values (101,’Amit, ‘S’, ‘Pune’);

ERROR at line 1:

ORA-00001: unique constraint (Amit.U_STUDENT_FNAME_LNAME) violated

 To DROP a UNIQUE Constraint:

 To Drop a UNIQUE Constraint, use the following SQL:

 Syntax:

 ALTER TABLE <Table_Name>

DROP CONSTRAINT <Constraint_Name>;

Example:

ALTER TABLE Student

DROP CONSTRAINT SYS_C005059;

ALTER TABLE Student

DROP CONSTRAINT U_Student2A_FName_LName;

Note: The above statement will drop the Unique Constraint ‘U_Student_FName_LName’ from the ‘Student’ Table.

SQL UNIQUE Constraint on ALTER TABLE:

To create a UNIQUE constraint on the “RollNo” Column when the table is already created, use below SQL:

 Syntax:

 ALTER TABLE <Table_Name>

ADD <Constraint_Type> (Column_Name);

ALTER TABLE Student

ADD Unique (RollNo);

Example 2:

ALTER TABLE Student MODIFY RollNo Number (5) UNIQUE;

ALTER TABLE Student MODIFY RollNo Unique;

To allow naming of a UNIQUE Constraint, and for defining a UNIQUE constraint on multiple columns, use the following

 Syntax:

 ALTER TABLE <Table_Name>

ADD CONSTRAINT <Constraint_Name> CONSTRAINT_TYPE (Column_Name);

Example:

ALTER TABLE Student

ADD CONSTRAINT U_Student_FName_Location UNIQUE (FName, Location);

Disable a UNIQUE CONSTRAINT:

 If we do not wish to DELETE the Unique Constraint as we may need the same in future but for some time we want the unique constraint not to function, then we can DISABLE the Unique Constraint.

Syntax:

 ALTER TABLE <Table_Name>

DISABLE CONSTRAINT <Constraint_Name>;

To find Constraint Enabled or Disabled:

Select Table_Name, Constraint_Type, Constraint_Name, Status, Generated from User_Constraints;

Example:

ALTER TABLE Student

DISABLE CONSTRAINT U_Student_FName_Location;

Here in the above ALTER Statement we have DISABLED the UNIQUE CONSTRAINT ‘<Constraint_Name>’ on the ‘Student’ Table.

Enabling a UNIQUE CONSTRAINT:

We can enable a unique constraint that has been disabled earlier, the syntax for enabling a unique constraint in Oracle SQL / PLSQL is:

 Syntax:

 ALTER TABLE <Table_Name>

ENABLE CONSTRAINT <Constraint_Name>;

Example:

ALTER TABLE Student

ENABLE CONSTRAINT U_Student_FName_Location;

Here in the above ALTER Statement we have ENABLED the UNIQUE CONSTRAINT ‘<Constraint_Name>’ on the ‘Student’ Table.

Hope This article will be helpful to the users to add the unique constraint in the table or to create new table with specified unique constraints. If you like this article or if you have any suggestions with this article kindly comment it in comment section.