Search This Blog

Monday, June 17, 2013

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.
Post a Comment