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 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:
ALTER TABLE CUSTOMERS DROP CONSTRAINT myUniqueConstraint;
If you are using MySQL, then you can use the following syntax:
ALTER TABLE CUSTOMERS DROP INDEX myUniqueConstraint;
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 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:
ALTER TABLE CUSTOMERS DROP CONSTRAINT myUniqueConstraint;
If you are using MySQL, then you can use the following syntax:
ALTER TABLE CUSTOMERS DROP INDEX myUniqueConstraint;