MySQL UNIQUE Constraint
Summary: in this tutorial, you will learn about MySQL UNIQUE constraint and how to use UNIQUE constraint to enforce the uniqueness of values in a column or a group of columns in a table.
Introduction to MySQL UNIQUE constraint
Sometimes, you want to ensure values in a column or a group of columns are unique. For example, email addresses of users in the users table, or phone numbers of customers in the customers table should be unique. To enforce this rule, you use a UNIQUE constraint.
A UNIQUE constraint is an integrity constraint that ensures values in a column or group of columns to be unique. A UNIQUE constraint can be either a column constraint or a table constraint.
To define a UNIQUE constraint for a column when you create a table, you use this syntax:
CREATE TABLE table_name(
...,
column_name data_type UNIQUE,
...
);Code language: SQL (Structured Query Language) (sql)
In this syntax, you include the UNIQUE keyword in the definition of the column that you want to enforce the uniqueness rule. If you insert or update a value that causes duplicate in the column_name, MySQL rejects the change and issues an error.
This UNIQUE constraint is a column constraint. And you can use it to enforce the unique rule for one column.
To define a UNIQUE constraint for two or more columns, you use the following syntax:
CREATE TABLE table_name(
...
column_name1 column_definition,
column_name2 column_definition,
...,
UNIQUE(column_name1,column_name2)
);Code language: SQL (Structured Query Language) (sql)
In this syntax, you add a comma-separated list of columns in parentheses after the UNIQUE keyword. MySQL uses the combination of values in both column column_name1 and column_name2 to evaluate the uniqueness.
If you define a UNIQUE constraint without specifying a name, MySQL automatically generates a name for it. To define a UNIQUE constraint with a name, you use this syntax:
[CONSTRAINT constraint_name]
UNIQUE(column_list)Code language: SQL (Structured Query Language) (sql)
In this syntax, you specify the name of the UNIQUE constraint after the CONSTRAINT keyword.
MySQL UNIQUE constraint example
First, creates a new table named suppliers with the two UNIQUE constraints:
CREATE TABLE suppliers (
supplier_id INT AUTO_INCREMENT,
name VARCHAR(255) NOT NULL,
phone VARCHAR(15) NOT NULL UNIQUE,
address VARCHAR(255) NOT NULL,
PRIMARY KEY (supplier_id),
CONSTRAINT uc_name_address UNIQUE (name , address)
);
Code language: SQL (Structured Query Language) (sql)
In this example, the first UNIQUE constraint is defined for the phone column:
phone VARCHAR(12) NOT NULL UNIQUECode language: SQL (Structured Query Language) (sql)
And the second constraint is for both name and address columns:
CONSTRAINT uc_name_address UNIQUE (name , address)Code language: SQL (Structured Query Language) (sql)
Second, insert a row into the suppliers table.
INSERT INTO suppliers(name, phone, address)
VALUES( 'ABC Inc',
'(408)-908-2476',
'4000 North 1st Street');Code language: SQL (Structured Query Language) (sql)
Third, attempt to insert a different supplier but has the phone number that already exists in the suppliers table.
INSERT INTO suppliers(name, phone, address)
VALUES( 'XYZ Corporation','(408)-908-2476','3000 North 1st Street');Code language: SQL (Structured Query Language) (sql)
MySQL issued an error:
Error Code: 1062. Duplicate entry '(408)-908-2476' for key 'phone'Code language: JavaScript (javascript)
Fourth, change the phone number to a different one and execute the insert statement again.
INSERT INTO suppliers(name, phone, address)
VALUES( 'XYZ Corporation','(408)-908-3333','3000 North 1st Street');Code language: SQL (Structured Query Language) (sql)
Fifth, insert a row into the suppliers table with values that already exist in the columns name and address :
INSERT INTO suppliers(name, phone, address)
VALUES( 'ABC Inc',
'(408)-908-1111',
'4000 North 1st Street');Code language: SQL (Structured Query Language) (sql)
MySQL issued an error because the UNIQUE constraint uc_name_address was violated.
Error Code: 1062. Duplicate entry 'ABC Inc-4000 North 1st Street' for key 'uc_name_address'Code language: JavaScript (javascript)
MySQL UNIQUE constraints and indexes
When you define a unique constraint, MySQL creates a corresponding UNIQUE index and uses this index to enforce the rule.
The SHOW CREATE TABLE statement shows the definition of the suppliers table:
SHOW CREATE TABLE suppliers;Code language: SQL (Structured Query Language) (sql)
As you can see from the output, MySQL created two UNIQUE indexes on the suppliers table: phone and uc_name_address.
The following SHOW INDEX statement displays all indexes associated with the suppliers table.
SHOW INDEX FROM suppliers;Code language: SQL (Structured Query Language) (sql)
Drop a unique constraint
To drop a UNIQUE constraint, you use can use DROP INDEX or ALTER TABLE statement:
DROP INDEX index_name ON table_name;Code language: SQL (Structured Query Language) (sql)
ALTER TABLE table_name
DROP INDEX index_name;Code language: SQL (Structured Query Language) (sql)
For example, the following statement drops the uc_name_address constraint on the suppliers table:
DROP INDEX uc_name_address ON suppliers;Code language: SQL (Structured Query Language) (sql)
Execute the SHOW INDEX statement again to verify if the uc_name_unique constraint has been removed.
SHOW INDEX FROM suppliers;Code language: SQL (Structured Query Language) (sql)
Add new unique constraint
The following ALTER TABLE ADD CONSTRAINT adds a unique constraint to a column of an existing table:
ALTER TABLE table_name
ADD CONSTRAINT constraint_name
UNIQUE (column_list);
Code language: SQL (Structured Query Language) (sql)
This statement adds a UNIQUE constraint uc_name_address back to the suppliers table:
ALTER TABLE suppliers
ADD CONSTRAINT uc_name_address
UNIQUE (name,address);Code language: SQL (Structured Query Language) (sql)
Note that MySQL will not add a unique constraint if the existing data in the columns of specified in the unique constraint does not comply with the uniqueness rule.
In this tutorial, you have learned how to use the MySQL UNIQUE constraint to enforce the uniqueness of values in a column or group of columns of a table.