unique index
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: unique index

  1. #1
    Join Date
    Jun 2001
    Posts
    45
    when i issue a update statement, it give me ORA-00001: unique constraint (owner.U1) violated.

    i want to avoid this constraint

  2. #2
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    you have duplicate keys, you cannot avoid unless you get rid of that unique constraint/index but then you are compromising your data integrity when you do so

  3. #3
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    You can temporarily disable constraint checking if you define your constraint as deferrable. Jeff has allready pointed you the corresponding manuals in your previous post regarding this same problem.

    But your unique constraint is probably built on top of unique index, so deferring constraint checking would be useless because it would fail immediately on unique index maintainance. So you'll have to recreate your unique constraint so that it will be implemented on top of non-unique index. Check the manuals on how to do that.
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  4. #4
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    but whatever you do if you end up with duplicate keys no matter what you do with your constraints/unique index you will still fail!

  5. #5
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    Well, one of the points with deferred constraints is that you can avoid situations where inside your transaction you are temporarily violating UK, but at the end of transaction there is no violation, ie all your data in unique column is again unique.

    Before the introduction of defered constraints it was sometimes very messy to perform many "atomic" updates on unique columns, although at the end of the transaction the constraint would not be violated.

    [Edited by jmodic on 01-02-2002 at 01:02 PM]
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  6. #6
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    yea I know deferred constraints disables temporary the constraints, what I mean is if you end up with duplicate keys then even you use deferred constraints you will get the error

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