A2-03-06.DDL-MySQL UNIQUE Constraint
转载自：http://www.mysqltutorial.org/mysql-unique-constraint/
MySQL UNIQUE Constraint
Summary: in this tutorial, you will learn about MySQL UNIQUE constraint to enforce the uniqueness of the values in a column or a group of columns.
Introduction to MySQL UNIQUE constraint
Sometimes, you want to enforce the uniqueness value in a column e.g., the phones of the suppliers in the suppliers table must be unique, or the combination of the supplier name and address must not be duplicate.
To enforce this rule, you need to use the UNIQUE constraint.
The UNIQUE constraint is either column constraint or table constraint that defines a rule that constrains values in a column or a group of columns to be unique.
To add the UNIQUE constraint to a column, you use the following syntax:
|
1
2
3
|
CREATE TABLE table_1(
column_name_1 data_type UNIQUE,
);
|
Or you can define the UNIQUE constraint as the table constraint as follows:
|
1
2
3
4
5
6
|
CREATE TABLE table_1(
...
column_name_1 data_type,
...
UNIQUE(column_name_1)
);
|
If you insert or update a value that causes a duplicate value in the column_name_1 column, MySQL will issue an error message and reject the change.
In case you want to enforce unique values across columns, you must define the UNIQUE constraint as the table constraint and separate the each column by a comma:
|
1
2
3
4
5
6
7
8
|
CREATE TABLE table_1(
...
column_name_1 data_type,
column_name_2 data type,
...
UNIQUE(column_name_1,column_name_2)
);
|
MySQL will use the combination of the values in both column_name_1 and column_name_2 columns to evaluate the uniqueness.
If you want to assign a specific name to a UNIQUE constraint, you use the CONSTRAINT clause as follows:
|
1
2
3
4
5
6
7
|
CREATE TABLE table_1(
...
column_name_1 data_type,
column_name_2 data type,
...
CONSTRAINT constraint_name UNIQUE(column_name_1,column_name_2)
);
|
MySQL UNIQUE constraint example
The following statement creates a new table named suppliers with the two UNIQUE constraints:
|
1
2
3
4
5
6
7
|
CREATE TABLE IF NOT EXISTS suppliers (
supplier_id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(255) NOT NULL,
phone VARCHAR(12) NOT NULL UNIQUE,
address VARCHAR(255) NOT NULL,
CONSTRAINT uc_name_address UNIQUE (name , address)
);
|
The first UNIQUE constraint is applied on the phone column. It means that every supplier must have a distinct phone number. In other words, no two suppliers have the same phone number.
The second UNIQUE constraint has a name uc_name_address that enforces the uniqueness of values in the name and address columns. It means suppliers can have the same name or address, but cannot have the same name and address.
Let’s insert some rows into the suppliers table to test the UNIQUE constraint.
The following statement inserts a row into to the suppliers table.
|
1
2
|
INSERT INTO suppliers(name, phone, address)
VALUES('ABC Inc', '408-908-2476','4000 North 1st Street, San Jose, CA, USA');
|
|
1
|
1 row(s) affected
|
We try to insert a different supplier but has the phone number that already exists in the supplierstable.
|
1
2
|
INSERT INTO suppliers(name, phone, address)
VALUES('XYZ Corporation', '408-908-2476','4001 North 1st Street, San Jose, CA, USA');
|
MySQL issued an error:
|
1
|
Error Code: 1062. Duplicate entry '408-908-2476' for key 'phone'
|
Let’s change the phone number to a different one and execute the insert statement again.
|
1
2
|
INSERT INTO suppliers(name, phone, address)
VALUES('XYZ Corporation', '408-908-2567','400 North 1st Street, San Jose, CA, USA');
|
|
1
|
1 row(s) affected
|
Now we execute the following INSERT statement to insert a row with the values in the name and address columns that already exists.
|
1
2
|
INSERT INTO suppliers(name, phone, address)
VALUES('XYZ Corporation', '408-908-102','400 North 1st Street, San Jose, CA, USA');
|
MySQL issued an error.
|
1
|
Error Code: 1062. Duplicate entry 'XYZ Corporation-400 North 1st Street, San Jose, CA, USA' for key 'name'
|
Because the UNIQUE constraint uc_name_address was violated.
Managing MySQL UNIQUE constraints
When you add a unique constraint to a table MySQL creates a corresponding BTREE index to the database. The following SHOW INDEX statement displays all indexes created on the suppliers table.
|
1
|
SHOW INDEX FROM classicmodels.suppliers;
|
As you see, there are two BTREE indexes corresponding to the two UNIQUE constraints created.
To remove a UNIQUE constraint, you use can use DROP INDEX or ALTER TABLE statement as follows:
|
1
|
DROP INDEX index_name ON table_name;
|
|
1
2
|
ALTER TABLE table_name
DROP INDEX index_name;
|
For example, to remove the uc_name_address constraint on the suppliers table, you the following statement:
|
1
|
DROP INDEX uc_name_address ON suppliers;
|
Execute the SHOW INDEX statement again to verify if the uc_name_unique constraint has been removed.
|
1
|
SHOW INDEX FROM classicmodels.suppliers;
|
What if you want to add a UNIQUE constraint to a table that already exists?
To do this, you use the ALTER TABLE statement as follows:
|
1
2
|
ALTER TABLE table_name
ADD CONSTRAINT constraint_name UNIQUE (column_list);
|
For example, to add the uc_name_address UNIQUE constraint back to the suppliers table, you use the following statement:
|
1
2
|
ALTER TABLE suppliers
ADD CONSTRAINT uc_name_address UNIQUE (name,address);
|
Note that the combination of values in the name and address columns must be unique in order to make the statement execute successfully.
In this tutorial, you have learned how to use the MySQL UNIQUE constraint to enforce the uniqueness of values in a column or a group of columns in a table.
