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, 'jane_smith@example.com', 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:
user_id |
username |
|
age |
---|---|---|---|
1 |
john_doe |
NULL |
25 |
2 |
NULL |
jane_smith@example.com |
30 |
Explanation:
- The unique constraint on username and email allows for multiple NULL values across rows.
- The third insert violates the unique constraint by attempting to insert a duplicate combination of username and NULL, resulting in an error.
Example 2: Altering an Existing Table to Add a Unique Constraint on NULL Columns
-- 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', 'alice@example.com', NULL);
INSERT INTO users (user_id, username, email, age) VALUES (5, 'bob_jones', 'bob@example.com', NULL);
-- Display the content of the table
SELECT * FROM users;
Output:
user_id |
username |
|
age |
---|---|---|---|
1 |
john_doe |
NULL |
25 |
2 |
NULL |
jane_smith@example.com |
30 |
4 |
alice_smith |
alice@example.com |
NULL |
5 |
bob_jones |
bob@example.com |
NULL |
Explanation:
- The age column is altered to have a unique constraint, allowing multiple NULL values.
- Rows with NULL values in the age column are successfully inserted.
Create Unique Constraint with NULL Columns in MySQL
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.
Contact Us