ITSourceCode.com

We Exist to Provide 100% Free Source Code and Tutorials

Custom Search

SQL UNIQUE Constraint

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

Check Your Domain Ranking

Leave a Reply

Your email address will not be published. Required fields are marked *

ITSourceCode.com © 2016 Frontier Theme

Subscribe For Latest Updates

Signup for our newsletter and get notified when we publish new articles for free!