Handling of violation of unique constraints in Oracle Database

According to the definition of NULL, NULL indicates unknown. Therefore, the results of two NULL comparisons are neither equal nor unequal, and the results are still unknown. According to this definition, the existence of multiple NULL values should not violate the unique constraint.

In fact, Oracle is also implemented as follows:

SQL> CREATE TABLE T (ID NUMBER );

The table has been created.

SQL> ALTER TABLE T ADD UNIQUE (ID );

The table has been changed.

SQL> INSERT INTO T VALUES (1 );

One row has been created.

SQL> INSERT INTO T VALUES (1 );

Insert into t values (1)

* Error in row 1st:

ORA-00001: violation of unique constraints (YANGTK. SYS_C007300)

SQL> INSERT INTO T VALUES (NULL );

One row has been created.

SQL> INSERT INTO T VALUES (NULL );

One row has been created.

SQL> INSERT INTO T VALUES (NULL );

One row has been created.

However, when the unique constraint is a compound field, the situation changes. According to the description in the Oracle document, the unique constraints on composite fields cannot be repeated if they are not empty. That is to say, if two fields constitute a unique constraint and one of the fields is empty, the values of the other fields cannot be repeated.

SQL> DROP TABLE T PURGE;

The table has been deleted.

SQL> CREATE TABLE T (ID NUMBER, ID2 NUMBER );

The table has been created.

SQL> ALTER TABLE T ADD UNIQUE (ID, ID2 );

The table has been changed.

SQL> INSERT INTO T VALUES (1, 1 );

One row has been created.

SQL> INSERT INTO T VALUES (1, NULL );

One row has been created.

SQL> INSERT INTO T VALUES (2, NULL );

One row has been created.

SQL> INSERT INTO T VALUES (1, NULL );

Insert into t values (1, NULL)

* Error in row 1st:

ORA-00001: violation of unique constraints (YANGTK. SYS_C007301)

SQL> INSERT INTO T VALUES (NULL, NULL );

One row has been created.

SQL> INSERT INTO T VALUES (NULL, NULL );

One row has been created.

SQL> INSERT INTO T VALUES (NULL, NULL );

One row has been created.

If all values are NULL, they are still the same as the unique constraint of a single field and will not be duplicated. However, if some values are NULL, as shown in the example above, as long as the non-NULL part is repeated, Oracle considers the constraint to be repeated.

This seems to be in conflict with the definition of NULL. The first time I checked the concept, I never figured out why Oracle was so implemented. But this time I looked at the concept again, I already figured it out.

Because the unique constraints of Oracle rely on indexes, and Oracle's BTREE index does not store NULL values, records with all the key values being NULL are not recorded in the index, therefore, the unique constraint will not be violated. For some NULL records, the index must record numerical values. Therefore, once the non-NULL part of the key value conflicts, oracle considers the violation of the unique constraint.

Oracle chose its own convenient method to implement it here, without fully implementing the unique constraint according to the definition of NULL.

1. Oracle Database File recovery and backup ideas
2. Five benefits of using stored procedures in database management
3. Analysis of Oracle Database management scripts