MySQL UNIQUE constraints

Last Updated: 18 June, 2023

In MySQL, the UNIQUE constraint is used to enforce uniqueness of values in one or more columns of a table. It ensures that the values in the specified columns are unique across all the rows in the table. If we try to insert or update a row with duplicate values in the unique column(s), MySQL will generate an error.

To create a unique constraint on one or more columns, we can use the UNIQUE keyword in the CREATE TABLE statement or modify an existing table using the ALTER TABLE statement.

Here's an example of creating a table with a unique constraint:

  CREATE TABLE my_table (
    id INT PRIMARY KEY,
    email VARCHAR(255) UNIQUE,
    username VARCHAR(50) UNIQUE
  );

In this example, the email and username columns have the UNIQUE constraint, which means that each value in these columns must be unique.

Alternatively, we can add a unique constraint to an existing table using the ALTER TABLE statement:

  ALTER TABLE my_table
  ADD CONSTRAINT unique_email UNIQUE (email);

This statement adds a unique constraint to the email column in the my_table table.

It's worth noting that a unique constraint can consist of one or more columns. In the examples above, we used single columns for simplicity, but we can specify multiple columns in the UNIQUE constraint to enforce uniqueness across combinations of values.

Remember that the UNIQUE constraint can also be combined with other constraints, such as the PRIMARY KEY constraint or the NOT NULL constraint, depending on your requirements.

When we define a UNIQUE constraint on a column, MySQL automatically creates a unique index on that column. This index enforces the uniqueness of values in the column and also provides faster retrieval of data.