About Deferrable UNIQUE and PRIMARY keys

[jamesz]

From Oracle Administrator's Guide, I know that Deferrable UNIQUE and PRIMARY keys all must use nonunique indexes.
But why?

[pando]

coz you can defer a constraint check but not unique index check

[oravijay]

Hi,

Basically whenever you create a column with Primary Key or Unique Key, Oracle creates a Unique index.
This is the one which prevents the user from inserting duplicate values. All these checks are done at the statement level. Since creating a constraint with DEFERRABLE violates this and checks for duplicate keys
at the transaction level, it is necessary for us to create a non-unique index.

Vijay.

[jamesz]

Thanks a lot.
But I still wonder why a nonunique index must be used to enforce the unique constraint instead of unique index,while the constraint is deferred ? Why does the unique index violates the deferred unique contraint? and How the non-unique index implement the unique value entered into the contrained column?

[anandkl]

As said by other collegues,the constraint will not be checked untill the transaction is commited, so till that time the duplicate values need to be present in the database,so in order to do this we need to create non-unique index.Once the transaction is commited, the non-unique index will implement the unique constraint by enforcing the unique contraint.

I hope u understood this.

regards
anandkl