ORACLE - UNIQUE Constraints and NULL Values

Technology:

ORACLE (10G and higher versions)

Language:

SQL

A UNIQUE CONSTRAINT is a single field or combination of fields that uniquely defines a record. 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.

Note: In ORACLE a UNIQUE constraint can not contain more than 32 columns.

Implementation

Consider the following example.

{codecitation class="brush: sql; gutter: true;" width="500px"}

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

{/codecitation}

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

{codecitation class="brush: sql; gutter: true;" width="500px"}

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

{/codecitation}

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

{codecitation class="brush: sql; gutter: true;" width="500px"}

{/codecitation}

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. As we can not insert NULL values to the primary keys there is no problem with the UNIQUE constraint.

Thank you

Aaruran

http://aaruran-ora.blogspot.com/