Using NonUnique Index to Enforce Uniqueness

Mark D Powell · *nix forums Guru Joined: 23 Apr 2005 Posts: 701

If you use a unique index to enforce the PK constrainst, which is how
all versions prior to version 8 worked, then if you attempt to insert a
duplicate key an error will be generated at the time the index is
updated. On the other hand if you use a non-unique index to support
the PK constraint then Oracle can delay issuing an error message until
the time of commit. This is the mechanism that allows deferrable
constraints. The enforcement of the constraint is deferred until
commit time. As long as all necessary operations are accomplished
within the transaction the transaction will succeed.

This allows design flexibility so that the developer can delete the
parent row that has child rows (FK) first and then delete the child
rows after instead of having to delete the children first and then go
back and delete the parent. It allows inserting child rows that do not
have a parent row first and then creating the parent row.

The mechanics of exactly how Oracle accomplishes this task as not
described in the documentation, but obviously the rdbms has a means of
keeping a record of which rows have been changed and for which the
constraints need to be checked.

In the version 8 manual Oracle recommended that all indexes be created
as non-unique and the constraints defined to use the existing indexes.
I do not think this recommendation appears in the newer manuals though
in the 10g manual Oracle does recommend expliciting creating the
indexes used to support constraint prior to defining the constraint.

If Oracle inserts a row into a table and then updates an index it
discovers if the key alreadys exists in the index when the index is
updated. All if takes is a simple logic check of "is there a PK or UK
constraint on this" and if so then indicate a duplicate condition which
can then be signaled either immediately or at commit depending on if
the constraint is deferred.

HTH -- Mark D Powell --

dd · *nix forums beginner Joined: 08 Jun 2005 Posts: 14

Oracle manual describes using non unique index to enforce unique constraint
so that the index will not be dropped with constraint is diabled.

But I dont understand why non unique index can enforce uniqueness?

Google