MySQL UNIQUE Constraint
In MySQL, UNIQUE constraint is used to ensure that all values in a column are unique or different. The UNIQUE constraint provides uniqueness of column data. In MySQL, you can have more than one UNIQUE constraints on each table.
MySQL UNIQUE Constraint example
The following MySQL, creates a UNIQUE constraint on the "custId" column when the "tblCustomer" table is created:
Example
CREATE TABLE tblCustomer (
custId int NOT NULL UNIQUE,
firstName varchar(255) NOT NULL,
lastName varchar(255) NOT NULL,
address varchar(255) NOT NULL
);
OR
CREATE TABLE tblCustomer (
custId int NOT NULL,
firstName varchar(255) NOT NULL,
lastName varchar(255) NOT NULL,
address varchar(255) NOT NULL,
UNIQUE(custId)
);
MySQL UNIQUE Constraint on multiple columns example
The following MySQL, creates a UNIQUE constraint on the "custId" and "firstName" columns when the "tblCustomer" table is created:
Example
CREATE TABLE tblCustomer (
custId int NOT NULL,
firstName varchar(255) NOT NULL,
lastName varchar(255) NOT NULL,
address varchar(255) NOT NULL,
CONSTRAINT UC_Customer UNIQUE (custId,firstName)
);
Note:
In the above example, we are using UNIQUE constraint on "custId" and "firstName" columns to ensure that all values in the columns "custId" and "firstName" are unique or different.
You can use MySQL Command Line Client to enforces column to NOT to accept NULL values.
MySQL UNIQUE Constraint on ALTER table example
The following MySQL, creates a UNIQUE constraint on the "custId" column when table already exists into the database:
Example
ALTER TABLE tblCustomer
ADD UNIQUE (custId);
OR
ALTER TABLE tblCustomer
ADD CONSTRAINT UC_Customer UNIQUE (custId,firstName);
MySQL DROP a UNIQUE Constraint example
The following MySQL, dropping UNIQUE constraint "UC_Customer" from "tblCustomer" table:
Example
ALTER TABLE tblCustomer
DROP INDEX UC_Customer;
OR
ALTER TABLE tblCustomer
DROP CONSTRAINT UC_Customer;