Copyright © tutorialspoint.com

# SQL -UNIQUE Constraint

The UNIQUE Constraint prevents two records from having identical values in a particular column. In the CUSTOMERS table, for example, you might want to prevent two or more people from having identical age.

# Example:

For example, the following SQL creates a new table called CUSTOMERS and adds five columns. Here AGE column is set to UNIQUE, so that you can not have two records with same age:

    CREATE TABLE CUSTOMERS(
           ID   INT              NOT NULL,
           NAME VARCHAR (20)     NOT NULL,
           AGE  INT              NOT NULL UNIQUE,
           ADDRESS  CHAR (25) ,
           SALARY   DECIMAL (18, 2),
           PRIMARY KEY (ID)
    );

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

    ALTER TABLE CUSTOMERS
       MODIFY AGE INT NOT NULL UNIQUE;

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

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

# DROP a UNIQUE Constraint:

To drop a UNIQUE constraint, use the following SQL:

    ALTER TABLE CUSTOMERS
       DROP CONSTRAINT myUniqueConstraint;

If you are using MySQL then you can use following syntax:

    ALTER TABLE CUSTOMERS
       DROP INDEX myUniqueConstraint;

Copyright © tutorialspoint.com