Using MySQL UNIQUE Index To Prevent Duplicates
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. Hence, 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
the statement is as follows:
CREATE UNIQUE INDEX index_name
ON table_name(index_column_1,index_column_2,...);
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,...)
);
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
a statement as follows:
ALTER TABLE table_name
ADD CONSTRAINT constraint_name UNIQUE KEY(column_1,column_2,...);
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
a 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)
);
If you use the SHOW INDEXES
the statement, you will see that MySQL created a UNIQUE
index for email
column.
SHOW INDEXES FROM contacts;
Let’s insert a row into the contacts
table.
INSERT INTO contacts(first_name,last_name,phone,email)
VALUES('John','Doe','(408)-999-9765','john.doe@mysqltutorial.org');
Now if you try to insert a row whose email is john.doe@mysqltutorial.org
, you will get an error message.
INSERT INTO contacts(first_name,last_name,phone,email)
VALUES('Johny','Doe','(408)-999-4321','john.doe@mysqltutorial.org');
Error Code: 1062. Duplicate entry 'john.doe@mysqltutorial.org' for key 'unique_email'
Suppose you want the 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);
Adding the following row into 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','john.d@mysqltutorial.org');
Error Code: 1062. Duplicate entry 'john-doe-(408)-999-9765' for key 'idx_name_phone'
In this tutorial, you have learned how to use the MySQL UNIQUE
index to prevent duplicate values in the database.
0 Comments
CAN FEEDBACK
Emoji