Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> Re: Using NonUnique Index to Enforce Uniqueness
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 -- Received on Wed Feb 23 2005 - 08:41:43 CST