SQL - UNIQUE Constraint
The UNIQUE Constraint prevents two records from having identical values in a column. In the CUSTOMERS table, for example, you might want to prevent two or more people from having an identical age.
For example, the following SQL query creates a new table called CUSTOMERS and adds five columns. Here, the AGE column is set to UNIQUE, so that you cannot have two records with the 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 the CUSTOMERS table has already been created, then to add a UNIQUE constraint to the AGE column. You would write a statement like the query that is given in the code block below.
ALTER TABLE CUSTOMERS MODIFY AGE INT NOT NULL UNIQUE;
You can also use the following syntax, which supports naming the constraint in 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 query.
ALTER TABLE CUSTOMERS DROP CONSTRAINT myUniqueConstraint;
If you are using MySQL, then you can use the following syntax −
ALTER TABLE CUSTOMERS DROP INDEX myUniqueConstraint;