ORACLE - UNIQUE Constraints and NULL Values

In Oracle UNIQUE constraint allows more than one NULL values to be inserted. ORACLE considers one NULL value is not equal to another NULL value.

Consider the following example.

CREATE TABLE test1 (

col1 VARCHAR2(2),
col2 VARCHAR2(2)
);

Table created

ALTER TABLE test1
ADD CONSTRAINT test_unique UNIQUE (col1);
Table altered

INSERT INTO test1
VALUES ('a', 'a');
1 row inserted

INSERT INTO test1
VALUES ('a', 'a');
ORA-00001: unique constraint (TEST_UNIQUE) violated

INSERT INTO test1
VALUES (NULL, 'a');
1 row inserted

INSERT INTO test1
VALUES (NULL, 'a');

1 row inserted

Now we will test the UNIQUE constraint with two columns (composite UNIQUE constraint).

ALTER TABLE test1

DROP CONSTRAINT test_unique;

TRUNCATE TABLE table1;

ALTER TABLE test1

ADD CONSTRAINT unique2 UNIQUE (col1,col2);

Insert null values to both columns.

INSERT INTO test1
VALUES (NULL, NULL);

1 row inserted

INSERT INTO test1
VALUES (NULL, NULL);

1 row inserted

But the results changes when we have only one NULL value for this composite UNIQUE constraint.

INSERT INTO test1

VALUES (NULL, 'a');

1 row inserted

INSERT INTO test1

VALUES (NULL, 'a');

ORA-00001: unique constraint (UNIQUE2) violated

When we create a UNIQUE constraint ORACLE creates a UNIQUE INDEX for this constraint. The NULL values are not included in the INDEX so ORACLE allows inserting many number of (NULL, NULL) value pairs to this table.