MySQL UNIQUE
MySQL UNIQUE Constraint
To guarantee that every value in a column is unique, apply the UNIQUE constraint.
A column’s or a group of columns’ uniqueness is ensured by the UNIQUE and PRIMARY KEY constraints.
A UNIQUE constraint is inherently included in a PRIMARY KEY constraint.
On the other hand, only one PRIMARY KEY constraint and numerous UNIQUE constraints are allowed per table.
UNIQUE Constraint on CREATE TABLE
When the “Persons” table is formed, the UNIQUE constraint on the “ID” column is created with the following SQL:
CREATE TABLE Persons (
ID int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Age int,
UNIQUE (ID)
);
To name a UNIQUE constraint, and to define a UNIQUE constraint on multiple columns, use the following SQL syntax:
CREATE TABLE Persons (
ID int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Age int,
CONSTRAINT UC_Person UNIQUE (ID,LastName)
);
UNIQUE Constraint on ALTER TABLE
Once the table has been established, use the following SQL to create a UNIQUE constraint on the “ID” column:
ALTER TABLE Persons
ADD UNIQUE (ID);
Use the following SQL syntax to construct a UNIQUE constraint on multiple columns and to name the constraint:
ALTER TABLE Persons
ADD CONSTRAINT UC_Person UNIQUE (ID,LastName);
DROP a UNIQUE Constraint
To remove a UNIQUE constraint, use the SQL code below:
ALTER TABLE Persons
DROP INDEX UC_Person;
