Unique constraint in MySQL ensures that each value in the column is unique. If a column contains a NULL value, it is also treated as a unique value, but if a column contains multiple NULL values it can not have a unique constraint.

In this article we will look over how we can Create unique constraints with NULL columns in MySQL, using the syntax, methods, and some of the examples that will help to understand the process.

What is a Unique Constraint?

A unique constraint ensures that all values in a column or a group of columns are distinct within a table. When a unique constraint is applied to a column, MySQL automatically creates a unique index on that column. However, when NULL values are involved, the uniqueness constraint can become tricky. By default, MySQL allows multiple NULL values in columns with a unique constraint.

Syntax:

The syntax for creating a unique constraint with NULL columns in MySQL involves specifying the UNIQUE keyword during table creation or altering an existing table:

CREATE TABLE table_name (
column1 INT,
column2 INT,
UNIQUE KEY unique_constraint (column1, column2)
);

Examples of Unique Constraints with NULL columns in MySQL

Let’s look at some of the examples on how to create unique constraint with NULL columns in MySQL.

Example 1: Creating a Table with Unique Constraint on NULL Columns

-- Create a table with a unique constraint on two columns, allowing NULL values
CREATE TABLE users (
    user_id INT PRIMARY KEY,
    username VARCHAR(50) UNIQUE,
    email VARCHAR(100) UNIQUE,
    age INT,
    UNIQUE KEY unique_constraint (username, email)
);

-- Insert rows with NULL values
INSERT INTO users (user_id, username, email, age) VALUES (1, 'john_doe', NULL, 25);
INSERT INTO users (user_id, username, email, age) VALUES (2, NULL, '[email protected]', 30);

-- Attempting to insert a duplicate row with NULL values will violate the unique constraint
INSERT INTO users (user_id, username, email, age) VALUES (3, 'john_doe', NULL, 28); -- Violation

-- Display the content of the table
SELECT * FROM users;

Output:

-- Alter an existing table to add a unique constraint on NULL columns
ALTER TABLE users
ADD UNIQUE KEY unique_constraint_age (age);

-- Insert rows with NULL values
INSERT INTO users (user_id, username, email, age) VALUES (4, 'alice_smith', '[email protected]', NULL);
INSERT INTO users (user_id, username, email, age) VALUES (5, 'bob_jones', '[email protected]', NULL);

-- Display the content of the table
SELECT * FROM users;