About Deferrable UNIQUE and PRIMARY keys
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: About Deferrable UNIQUE and PRIMARY keys

Hybrid View

  1. #1
    Join Date
    May 2002
    Posts
    2

    Question

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

  2. #2
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    coz you can defer a constraint check but not unique index check

  3. #3
    Join Date
    Mar 2002
    Posts
    301
    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.
    Say No To Plastics

  4. #4
    Join Date
    May 2002
    Posts
    2

    Question

    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?


  5. #5
    Join Date
    Aug 2001
    Location
    chennai,bangalore
    Posts
    840
    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

    anandkl

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width