MySQL UNIQUE Constraint: Basics to Advanced for Beginners

1 1. Introduction
2 2. Basics of the UNIQUE Constraint
3 3. How to Use UNIQUE Constraints
4 4. Points to Note About UNIQUE Constraints
5 5. Frequently Asked Questions (FAQ)
6 6. Summary

1. Introduction

In database design, maintaining data uniqueness is extremely important. If the same data is registered multiple times, it can cause the system to malfunction and break consistency. MySQL provides a UNIQUE constraint to prevent this issue. For example, when registering users for a web service, if the same email address is registered multiple times, it can cause errors during login. To prevent this, using a UNIQUE constraint ensures that the values in a specific column remain unique. In this article, we will clearly explain MySQL’s UNIQUE constraint, covering everything from basic usage to advanced examples. We will also address cautions and frequently asked questions, aiming to provide content you can apply directly in practice.

2. Basics of the UNIQUE Constraint

What is a UNIQUE constraint?

UNIQUE constraint is a rule in MySQL that prohibits duplicate values in a specific column or combination of columns. By applying this constraint, you can ensure that the same value can never be inserted again.

Differences from PRIMARY KEY

MySQL also has a PRIMARY KEY constraint, but there are several differences from the UNIQUE constraint.

PRIMARY KEY is always NOT NULL PRIMARY KEY not only guarantees uniqueness but also prohibits values from being NULL. In contrast, the UNIQUE constraint allows NULL values.
Only one PRIMARY KEY Each table can have only one PRIMARY KEY, whereas you can define multiple UNIQUE constraints within the same table.

When UNIQUE constraints are used

The UNIQUE constraint is commonly used in situations such as:

Registering email addresses or usernames When each user must have a unique email address.
Managing product numbers or order numbers When product numbers or order numbers must not be duplicated.
Composite condition constraints When you want to guarantee uniqueness by combining two or more columns.

3. How to Use UNIQUE Constraints

Set When Creating a Table

In MySQL, you can set a UNIQUE constraint directly when creating a new table.

Example Applying to a Single Column

CREATE TABLE users (
    id INT AUTO_INCREMENT,
    email VARCHAR(255) UNIQUE,
    PRIMARY KEY (id)
);

In this example, the email column has a UNIQUE constraint applied. Attempting to register the same email address multiple times will result in an error.

Example of Multiple Columns (Composite Unique Constraint)

CREATE TABLE orders (
    order_id INT AUTO_INCREMENT,
    product_id INT,
    user_id INT,
    UNIQUE (product_id, user_id),
    PRIMARY KEY (order_id)
);

In this example, the combination of product_id and user_id is guaranteed to be unique. It is used to prevent the same user from ordering the same product multiple times.

Add Constraints to an Existing Table

You can also add a UNIQUE constraint to an existing table.

Example Adding a Constraint to a Single Column

ALTER TABLE users
ADD CONSTRAINT unique_email UNIQUE (email);

Example Adding a Composite Constraint

ALTER TABLE orders
ADD CONSTRAINT unique_product_user UNIQUE (product_id, user_id);

How to Remove Constraints

To remove existing constraints, use the following SQL.

Removing a Single-Column Constraint

ALTER TABLE users
DROP INDEX unique_email;

Removing a Composite Constraint

ALTER TABLE orders
DROP INDEX unique_product_user;

4. Points to Note About UNIQUE Constraints

Handling NULL Values

The UNIQUE constraint applies special rules to columns that contain NULL values. The UNIQUE constraint does not raise an error even if multiple NULL values exist in the same column. This is because MySQL treats NULL values as “not unique.” Example:

CREATE TABLE test_table (
    id INT AUTO_INCREMENT,
    column1 INT UNIQUE,
    PRIMARY KEY (id)
);

Let’s insert the following data into this table:

INSERT INTO test_table (column1) VALUES (NULL);
INSERT INTO test_table (column1) VALUES (NULL);

Even in this case, no error occurs. This is because NULL values are considered “not equal to anything.”

Impact on Performance

When you set a UNIQUE constraint, MySQL creates an internal index. This index helps with data searching and sorting, but performance can degrade when inserting or updating large amounts of data.

Note 1: When inserting a large amount of data at once, the UNIQUE constraint check is performed, which can slow down processing.
Note 2: To optimize the index, you should consider rebuilding the index before the data volume grows too large.

How to Handle Errors

When a UNIQUE constraint violation occurs, MySQL returns error code “1062”. This error occurs when you try to insert duplicate data. Example:

INSERT INTO users (email) VALUES ('test@example.com');
INSERT INTO users (email) VALUES ('test@example.com');

In this case, an error occurs on the second insert. Solution:

To prevent the error in advance, check whether the data already exists before inserting.

   SELECT COUNT(*) FROM users WHERE email = 'test@example.com';

Use ON DUPLICATE KEY UPDATE to update the data when a duplicate occurs.

   INSERT INTO users (email) VALUES ('test@example.com')
   ON DUPLICATE KEY UPDATE email = 'test@example.com';

5. Frequently Asked Questions (FAQ)

Q1: What is the difference between a UNIQUE constraint and a PRIMARY KEY?

PRIMARY KEY guarantees uniqueness within a table and does not allow NULL values. Also, only one can be set per table. On the other hand, a UNIQUE constraint can be applied to multiple columns and allows NULL values.

Q2: What happens when you apply a UNIQUE constraint to a column that contains NULL values?

You can insert multiple NULL values. This is because MySQL interprets NULL values as incomparable.

Q3: In what situations are composite unique constraints used?

For example, it is used when you want to ensure that the combination of product_id and user_id is unique. It helps prevent the same user from ordering the same product multiple times.

Q4: Can you add a UNIQUE constraint if there are duplicate rows in existing data?

No, you cannot add a UNIQUE constraint if duplicate data exists. You need to delete or correct the duplicate rows before adding the constraint.

Q5: Is the index also removed when the constraint is dropped?

Yes, dropping a UNIQUE constraint also removes the associated index.

6. Summary

MySQL’s UNIQUE constraint is a very handy feature that guarantees data uniqueness. By understanding the differences from PRIMARY KEY and how NULL values are handled, and using it correctly, you can improve the quality of your database design. In particular, properly leveraging the UNIQUE constraint is essential for maintaining database integrity. Please refer to the methods and cautions introduced in this article to help with your design and implementation. In the next article, we will discuss in detail how to use indexes in MySQL and other constraints such as FOREIGN KEY and CHECK. Stay tuned!