Italian Trulli

UNIQUE Constraint in SQL - SQL UNIQUE Constraint

Test Tribe
.
 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 of Unique Constraints
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

UNIQUE Constraint in SQL With Example

Post a Comment

0 Comments