Example of Create unique constraint with NULL columns in SQL
Suppose we have a table named “employees” with the following structure:
CREATE TABLE employees (
employee_id INT PRIMARY KEY,
first_name VARCHAR(50),
last_name VARCHAR(50),
email VARCHAR(100) UNIQUE
);
Now, imagine you want to change this table. You want only one email for each row. But you also want to let some rows have no email.
Step 1: Alter the Table
ALTER TABLE employees
ADD CONSTRAINT unique_email UNIQUE (email);
This SQL statement adds a unique constraint named unique_email on the email column of the employees table.
Step 2: Test with Data
Let’s insert some sample data into the employees table to test the unique constraint:
INSERT INTO employees (employee_id, first_name, last_name, email)
VALUES
(1, 'John', 'Doe', 'john.doe@example.com'),
(2, 'Jane', 'Smith', NULL),
(3, 'Michael', 'Johnson', NULL);
In this data insertion, we have two employees with NULL values in the email column.
Step 3: Validate the Constraint
INSERT INTO employees (employee_id, first_name, last_name, email)
VALUES
(4, 'Emily', 'Davis', 'john.doe@example.com');
You cannot save this email address here. This is not allowed. You must give a new email address. The email address you typed is already used. Each email address must be different. The system does not let you use.
This lead to an error:
Error Code: 1062. Duplicate entry 'john.doe@example.com' for key 'employees.email'
Step 4: Verify NULL Behavior
Now, let’s check if the unique constraint allows NULL values:
INSERT INTO employees (employee_id, first_name, last_name, email)
VALUES
(5, 'Chris', 'Anderson', NULL);
This insertion should succeed because NULL values are treated as distinct, and the constraint only enforces uniqueness on non-NULL values.
By following these steps, you can create a unique constraint with NULL columns in SQL and ensure data integrity while allowing flexibility for NULL values.
How to Create Unique Constraint with NULL Columns in SQL
In SQL databases, maintaining data integrity is crucial, and one common requirement is applying uniqueness among certain columns. However, handling NULL values in these columns can be challenging. By creating a unique constraint with NULL columns, we can ensure that non-NULL values are unique while allowing multiple rows with NULL values.
In this article, We will explores how to create such a constraint in SQL, ensuring data integrity and consistency in database design and so on.