UNIQUE Constraint
UNIQUE Constraint
The UNIQUE constraint ensures that all values in a column are different.
Both the UNIQUE and PRIMARY KEY constraints provide a guarantee for uniqueness for a column or set of columns.
A PRIMARY KEY constraint automatically has a UNIQUE constraint.
However, you can have many UNIQUE constraints per table, but only one PRIMARY KEY constraint per table.
This constraint ensures that all values inserted into the column will be unique. It means a column cannot stores duplicate values. MySQL allows us to use more than one column with UNIQUE constraint in a table. The below statement creates a table with a UNIQUE constraint:
- mysql> CREATE TABLE ShirtBrands(Id INTEGER, BrandName VARCHAR(40) UNIQUE, Size VARCHAR(30));
Execute the queries listed below to understand how it works:
- mysql> INSERT INTO ShirtBrands(Id, BrandName, Size) VALUES(1, ‘Pantaloons’, 38), (2, ‘Cantabil’, 40);
- mysql> INSERT INTO ShirtBrands(Id, BrandName, Size) VALUES(1, ‘Raymond’, 38), (2, ‘Cantabil’, 40);
Output
In the below output, we can see that the first INSERT query executes correctly, but the second statement fails and gives an error that says: Duplicate entry ‘Cantabil’ for key BrandName.