Handling violations of unique constraints in Oracle databases

According to the null definition, NULL represents an unknown, so the result of two null comparisons is neither equal nor unequal, and the result is still unknown. Based on this definition, the existence of multiple null values should not violate a unique constraint.

In fact, Oracle is also implemented like this:

Sql> CREATE TABLET (ID Number); The table was created. SQL> ALTER TABLETADD UNIQUE(ID); the table has changed. SQL> INSERT  intoTVALUES(1); created1line. SQL> INSERT  intoTVALUES(1);INSERT  intoTVALUES(1)*The1line error: ORA-00001: Violates the unique constraint (YANGTK. sys_c007300) SQL> INSERT  intoTVALUES(NULL); created1line. SQL> INSERT  intoTVALUES(NULL); created1line. SQL> INSERT  intoTVALUES(NULL); created1Yes.

However, when the unique constraint is a composite field, the situation changes. Based on the description of the Oracle document, the value of a non-empty field cannot be duplicated for a unique constraint on a composite field. That is, if two fields form a unique constraint where one field is empty, the value of the other field cannot be duplicated.

Sql> DROP TABLET PURGE; The table has been deleted. SQL> CREATE TABLET (ID Number, ID2 Number); The table was created. SQL> ALTER TABLETADD UNIQUE(ID, ID2); The table has changed. SQL> INSERT  intoTVALUES(1,1); created1line. SQL> INSERT  intoTVALUES(1,NULL); created1line. SQL> INSERT  intoTVALUES(2,NULL); created1line. SQL> INSERT  intoTVALUES(1,NULL);INSERT  intoTVALUES(1,NULL)*The1line error: ORA-00001: Violates the unique constraint (YANGTK. sys_c007301) SQL> INSERT  intoTVALUES(NULL,NULL); created1line. SQL> INSERT  intoTVALUES(NULL,NULL); created1line. SQL> INSERT  intoTVALUES(NULL,NULL); created1Yes.

View Code

For all null cases, the same as the single field constraint does not cause duplication, but for partially null cases, as shown in the example above, Oracle considers the constraint to be duplicated as long as the non-null part is duplicated.

And this seems to be in conflict with the definition of NULL, the first time I looked at concept did not understand why Oracle is so realized, but this time to look at concept, has been trying to understand.

Because Oracle's unique constraint is dependent on the index implementation, and Oracle's Btree index does not store null values, records with all null keys are not recorded in the index, so there is no violation of the unique constraint, and for partially null records, the index is to record the value, So once the non-null part of the key value has clashed, Oracle considers the unique constraint violated.

Oracle has chosen its own convenient approach here, without fully implementing a unique constraint based on the null definition.