A2-03-06.DDL-MySQL UNIQUE Constraint

时间:2021-10-09 16:25:52

转载自: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:

Or you can define the UNIQUE constraint as the table constraint as follows:

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:

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:

MySQL UNIQUE constraint example

The following statement creates a new table named suppliers with the two UNIQUE constraints:

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.

 

We try to insert a different supplier but has the phone number that already exists in the supplierstable.

MySQL issued an error:

Let’s change the phone number to a different one and execute the insert statement again.

 

Now we execute the following INSERT statement to insert a row with the values in the name and address columns that already exists.

MySQL issued an error.

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.

A2-03-06.DDL-MySQL UNIQUE Constraint

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:

 

For example, to remove the uc_name_address constraint on the suppliers table, you the following statement:

Execute the SHOW INDEX statement again to verify if the uc_name_unique constraint has been removed.

A2-03-06.DDL-MySQL UNIQUE Constraint

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:

For example, to add the uc_name_address UNIQUE constraint back to the suppliers table, you use the following statement:

A2-03-06.DDL-MySQL UNIQUE Constraint

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.