UNIQUE Constraint in SQL - SQL UNIQUE Constraint

 UNIQUE Constraint in SQL - How to use UNIQUE Constraint in SQL?

In SQL UNIQUE constraint ensures that all values in a column are different to each other. While creating a table if the developer defines a column with a UNIQUE constraint this means that the Column will not accept the duplicate value. 

Syntax - Syntax of UNIQUE Constraint is given below

UNIQUE Constraint on CREATE TABLE

SQL Server / Oracle / MS Access:

CREATE TABLE Student ( ID int NOT NULL UNIQUE, LastName varchar(250) NOT NULL, FirstName varchar(250), Age int );

MySQL:

CREATE TABLE Student ( ID int NOT NULL, LastName varchar(250) NOT NULL, FirstName varchar(250), Age int, UNIQUE (ID) );
The Above SQL query will create a unique constraint for the ID column when the Student table is created. The mean ID column of the student table will always accept a unique value.

For Multiple columns - To make Multiple columns Unique use the below SQL query.

SQL Server / Oracle / MS Access/MySQL :

CREATE TABLE Student ( ID int NOT NULL, LastName varchar(255) NOT NULL, FirstName varchar(255), Age int, CONSTRAINT UC_Student UNIQUE (ID,LastName) );

The above query will create a unique constraint for ID and LastName Column when the student table is created.

UNIQUE Constraint on ALTER TABLE

Syntax

MySQL / SQL Server / Oracle / MS Access:

ALTER TABLE Persons ADD UNIQUE (ID);

For Multiple Column
SQL Server / Oracle / MS Access/MySQL :

ALTER TABLE Persons ADD CONSTRAINT UC_Student UNIQUE (ID,LastName);

DROP a UNIQUE Constraint

SQL Server / Oracle / MS Access:

ALTER TABLE Persons DROP CONSTRAINT UC_Student;

MySQL:

ALTER TABLE Persons DROP INDEX UC_Student;
 

Hope!!! The above Tutorial on SQL UNIQUE Constraint is helpful for you...

Team,
QA acharya


Team- QA acharya

Post a Comment

Previous Post Next Post