SQL UNIQUE Constraint

0
150

sqltuts

This unique constraint identifies each record in a table. A PRIMARY KEY constraint itself defines UNIQUE constraint. The basic difference between UNIQUE and PRIMARY KEY constraint is that we can have many UNIQUE constraints on a table whereas we can have only one PRIMARY KEY.

 

Example of UNIQUE constraint on CREATE TABLE

 

[sql] CREATE TABLE Employee
(
Id int NOT NULL UNIQUE,
Name varchar(50) UNIQUE,
Address varchar(50),
);
[/sql]  

Example of UNIQUE constraint on ALTER TABLE

 
[sql] ALTER TABLE Employee
ADD UNIQUE(E_Id)
[/sql]  

Example of DROP a UNIQUE Constraint

 
[sql] ALTER TABLE Employee
DROP INDEX uc_Employee_Id
[/sql]  

 
[sql] CREATE TABLE Producers(
ID INT NOT NULL,
FNAME VARCHAR (20) NOT NULL,
LNAME VARCHAR (20) NOT NULL
AGE INT NOT NULL UNIQUE,
ADDRESS CHAR (25) , NOT NULL
Gender VARCHAR(10), NOT NULL
SALARY DECIMAL (18, 2),
PRIMARY KEY (ID)
);
[/sql]

If PRODUCERS table has already been created, then to add a UNIQUE constraint to AGE column, you would write a statement similar to the following:

[sql] ALTER TABLE CUSTOMERS
MODIFY AGE INT NOT NULL UNIQUE;
[/sql]

You can also use following syntax, which supports naming the constraint in multiple columns as well:

[sql] ALTER TABLE CUSTOMERS
ADD CONSTRAINT myUniqueConstraint UNIQUE(AGE, SALARY);
[/sql]  

CREATE UNIQUE CONSTRAINT – USING AN ALTER TABLE STATEMENT

The syntax for creating a unique constraint using an ALTER TABLE statement in MySQL is:

[sql] ALTER TABLE table_name
ADD CONSTRAINT constraint_name UNIQUE (column1, column2, … column_n);
[/sql] table_name

The name of the table to modify. This is the table that you wish to add a unique constraint to.

constraint_name

The name of the unique constraint.

column1, column2, … column_n

The columns that make up the unique constraint.

 

DROP UNIQUE CONSTRAINT

The syntax for dropping a unique constraint in MySQL is:

[sql] ALTER TABLE table_name
DROP INDEX constraint_name;
[/sql]

table_name

The name of the table to modify. This is the table that you wish to remove the unique constraint from.

constraint_name

The name of the unique constraint to remove.

Facebook Comments

LEAVE A REPLY

Please enter your comment!
Please enter your name here

This site uses Akismet to reduce spam. Learn how your comment data is processed.