DEV Community

Pranav Bakare
Pranav Bakare

Posted on

Can we have multiple null values in a unique key column?

Yes, in Oracle SQL, you can have multiple NULL values in a column that is defined as a UNIQUE key. This behavior is in line with the SQL standard, which treats NULL values as distinct when enforcing uniqueness.

Why is this allowed?

NULLs are not equal to each other: In SQL, a NULL represents the absence of a value, and the standard SQL interpretation is that NULL is not equal to NULL. This means that when checking for uniqueness, each NULL value is treated as a distinct value, allowing multiple NULLs in a unique column.

Example:

If you define a column with a UNIQUE constraint, Oracle allows multiple rows where this column has NULL values.

CREATE TABLE employees (
emp_id NUMBER PRIMARY KEY,
emp_name VARCHAR2(100),
dept_id NUMBER,
CONSTRAINT unique_dept UNIQUE (dept_id)
);

You can insert rows with multiple NULL values in the dept_id column:

INSERT INTO employees (emp_id, emp_name, dept_id) VALUES (1, 'Alice', NULL);
INSERT INTO employees (emp_id, emp_name, dept_id) VALUES (2, 'Bob', NULL);
INSERT INTO employees (emp_id, emp_name, dept_id) VALUES (3, 'Charlie', 101);

In the above example, two rows with NULL values in the dept_id column are allowed because NULLs are considered distinct.

Key Points:

NULLs are distinct: Multiple NULLs can exist in a column with a UNIQUE constraint because they are not compared as equal to each other.

Non-NULL values are unique: If a non-NULL value is inserted, it must be unique among all other non-NULL values in the column.

Caveats:

This behavior might differ in some databases (e.g., SQL Server treats NULLs as equal in the context of unique constraints, so only one NULL value would be allowed).

If you want to restrict the number of NULL values in a column, you would need to use triggers or check constraints to enforce this.


Let me know if you need more examples or further clarification on this topic!

Image of Timescale

🚀 pgai Vectorizer: SQLAlchemy and LiteLLM Make Vector Search Simple

We built pgai Vectorizer to simplify embedding management for AI applications—without needing a separate database or complex infrastructure. Since launch, developers have created over 3,000 vectorizers on Timescale Cloud, with many more self-hosted.

Read more →

Top comments (0)

Postmark Image

Speedy emails, satisfied customers

Are delayed transactional emails costing you user satisfaction? Postmark delivers your emails almost instantly, keeping your customers happy and connected.

Sign up