Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: Using NonUnique Index to Enforce Uniqueness

Re: Using NonUnique Index to Enforce Uniqueness

From: Mark D Powell <Mark.Powell_at_eds.com>
Date: 23 Feb 2005 06:41:43 -0800
Message-ID: <1109169703.766603.76410@l41g2000cwc.googlegroups.com>


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

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US