UNIQUE constraint ensures that all values in a column are different.
PRIMARY KEY constraints provide a guarantee for uniqueness for a column or set of columns.
PRIMARY KEY constraint automatically has a
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);
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.