Summary: in this tutorial, you will learn how to use the MySQL UNIQUE index to prevent duplicate values in one or more columns in a table.
Introduction to the MySQL UNIQUE index
To enforce the uniqueness value of one or more columns, you often use the PRIMARY KEY constraint. However, each table can have only one primary key. So if you want to have more than one column or a set of columns with unique values, you cannot use the primary key constraint.
Luckily, MySQL provides another kind of index called UNIQUE index that allows you to enforce the uniqueness of values in one or more columns. Unlike the PRIMARY KEY index, you can have more than one UNIQUE index per table.
To create a UNIQUE index, you use the CREATE UNIQUE INDEX statement as follows:
CREATE UNIQUE INDEX index_name
ON table_name(index_column_1,index_column_2,...);Code language: SQL (Structured Query Language) (sql)Another way to enforce the uniqueness of value in one or more columns is to use the UNIQUE constraint.
When you create a UNIQUE constraint, MySQL creates a UNIQUE index behind the scenes.
The following statement illustrates how to create a unique constraint when you create a table.
CREATE TABLE table_name(
...
UNIQUE KEY(index_column_,index_column_2,...)
);Code language: SQL (Structured Query Language) (sql)In this statement, you can also use the UNIQUE INDEX instead of the UNIQUE KEY because they are synonyms.
If you want to add a unique constraint to an existing table, you can use the ALTER TABLE statement as follows:
ALTER TABLE table_name
ADD CONSTRAINT constraint_name UNIQUE KEY(column_1,column_2,...);Code language: SQL (Structured Query Language) (sql)MySQL UNIQUE Index & NULL
Unlike other database systems, MySQL considers NULL values as distinct values. Therefore, you can have multiple NULL values in the UNIQUE index.
This is how MySQL was designed. It is not a bug even though it was reported as a bug.
Another important point is that the UNIQUE constraint does not apply to NULL values except for the BDB storage engine.
MySQL UNIQUE index examples
Suppose you want to manage contacts in an application. You also want the email of every contact in the contacts table must be unique.
To enforce this rule, you create a unique constraint in the CREATE TABLE statement as follows:
CREATE TABLE IF NOT EXISTS contacts (
id INT AUTO_INCREMENT PRIMARY KEY,
first_name VARCHAR(50) NOT NULL,
last_name VARCHAR(50) NOT NULL,
phone VARCHAR(15) NOT NULL,
email VARCHAR(100) NOT NULL,
UNIQUE KEY unique_email (email)
);Code language: SQL (Structured Query Language) (sql)If you use the SHOW INDEXES statement, you will see that MySQL created a UNIQUE index for email column.
SHOW INDEXES FROM contacts;Code language: SQL (Structured Query Language) (sql)
Let’s insert a row into the contacts table.
INSERT INTO contacts(first_name,last_name,phone,email)
VALUES('John','Doe','(408)-999-9765','[email protected]');Code language: SQL (Structured Query Language) (sql)Now if you try to insert a row whose email is [email protected], you will get an error message.
INSERT INTO contacts(first_name,last_name,phone,email)
VALUES('Johny','Doe','(408)-999-4321','[email protected]');Code language: SQL (Structured Query Language) (sql)Error Code: 1062. Duplicate entry '[email protected]' for key 'unique_email'Code language: SQL (Structured Query Language) (sql)Suppose you want a combination of first_name, last_name, and phone is also unique among contacts. In this case, you use the CREATE INDEX statement to create a UNIQUE index for those columns as follows:
CREATE UNIQUE INDEX idx_name_phone
ON contacts(first_name,last_name,phone);Code language: SQL (Structured Query Language) (sql)Adding the following row to the contacts table causes an error because the combination of the first_name, last_name, and phone already exists.
INSERT INTO contacts(first_name,last_name,phone,email)
VALUES('john','doe','(408)-999-9765','[email protected]');Code language: SQL (Structured Query Language) (sql)Error Code: 1062. Duplicate entry 'john-doe-(408)-999-9765' for key 'idx_name_phone'Code language: SQL (Structured Query Language) (sql)In this tutorial, you have learned how to use the MySQL UNIQUE index to prevent duplicate values in the database.
