SQL- UNIQUE CONSTRAINT WITH EXAMPLES

UNIQUE constraint uniquely identifies each record in a database table.The UNIQUE and PRIMARY KEY constraints both provide a guarantee for uniqueness for a column or set of columns.A PRIMARY KEY constraint automatically has a UNIQUE constraint defined on it. you can have many UNIQUE constraints per table, but only one PRIMARY KEY constraint per table.

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;