SQL UNIQUE Constraint

0
27

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

 

  1. CREATE TABLE Employee
  2.     (
  3.     Id int NOT NULL UNIQUE,
  4.     Name varchar(50) UNIQUE,
  5.     Address varchar(50),
  6.     );

 

Example of UNIQUE constraint on ALTER TABLE

 

  1. ALTER TABLE Employee
  2. ADD UNIQUE(E_Id)

 

Example of DROP a UNIQUE Constraint

 

  1. ALTER TABLE Employee
  2. DROP INDEX uc_Employee_Id

 

 

  1. CREATE TABLE Producers(
  2.        ID   INT              NOT NULL,
  3.        FNAME VARCHAR (20)     NOT NULL,
  4.        LNAME VARCHAR (20)     NOT NULL
  5.        AGE  INT              NOT NULL UNIQUE,
  6.        ADDRESS  CHAR (25) ,  NOT NULL
  7.        Gender VARCHAR(10),   NOT NULL
  8.        SALARY   DECIMAL (18, 2),      
  9.        PRIMARY KEY (ID)
  10. );

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:

  1. ALTER TABLE CUSTOMERS
  2.    MODIFY AGE INT NOT NULL UNIQUE;

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

  1. ALTER TABLE CUSTOMERS
  2.    ADD CONSTRAINT myUniqueConstraint UNIQUE(AGE, SALARY);

 

CREATE UNIQUE CONSTRAINT – USING AN ALTER TABLE STATEMENT

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

  1. ALTER TABLE table_name
  2. ADD CONSTRAINT constraint_name UNIQUE (column1, column2, ... column_n);

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:

  1. ALTER TABLE table_name
  2. DROP INDEX constraint_name;

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
(Visited 33 times, 1 visits today)

LEAVE A REPLY

Please enter your comment!
Please enter your name here