Re: Index Creation Questions

Mark Stock wrote: >Schultheis, Carol L wrote: >> 1. what is the difference between creating a unique index on a column >> via the create unique index statement >> versus using the primary key constraint when creating the table. > >The second one creates a unique index AND allows you to use referential >integrity. I didn't see the origunal post but from what I see, the whole question has not been answered. There is a fundamental difference in the way the engine treats an index as opposed to the way it treats a constraint. When I am running an operation like: update tab set key_num = key_num + 1if there is a unique index on column key_num, the above is very likely to cause a violation and cause the statement to roll back. (Not necessarily a whole transaction but that opens a whole other can o'worms.) If there were a uniqueness constraint, the engine woule blithely continue updating key_num columns on the table until the statement is finished executing. Then, if the constraint is still being violated (in this case the violation would have gone away) the engine will roll back the statement. This is known as "immediate constraint checking". Dave knows this, of course, but perhaps Carol needed it pointed out. -- -- Jake (Trying to keep those pesky worms in the can..) +------------------------------------------------------------+ | The expedient performance of a task with excessive concern | | regarding its duration-to-completion engenders a virtual | | certainty of diminished benefit therefrom. | | -- Benjamin Franklin (but he said it in 3 words) | +------------------------------------------------------------+