unique contraint issue
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 10 of 10

Thread: unique contraint issue

  1. #1
    Join Date
    Sep 2000
    Location
    Chicago, IL
    Posts
    316
    I am trying to copy data from DB-A to DB-B

    I have created the table in DB-B; exactly that same as DB-A. It has a primary key on it. That key is EXACTLY the same on both.

    When I insert into DB-B from DB-A it bombs out with a UNIQUE CONTRAINT error.

    Q: How come the table in DB-A with the same exact primary key have data that does not meet the requirements of the primary key contraint ?

  2. #2
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    db-a
    data: 1, 2, 3, 4

    db-b
    data: 1, 2, 3, 4

    of coz you get unique constraint error

  3. #3
    Join Date
    Jun 2002
    Posts
    65
    You are not allowed to have duplicate values...coz of Primary key

  4. #4
    Join Date
    Sep 2000
    Location
    Chicago, IL
    Posts
    316
    I know that !!

    The questions is:
    How come the table in DB-A has data that violates the PK ?

  5. #5
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    you said you are inserting from a to b, in any case you will get that error by logic unless you have enabled PK with novalidate instruction

    you said you create the table in b as a, you have not said if you have created it empty or with data

    so basically I dont know what you are talking about

  6. #6
    Join Date
    Sep 2000
    Location
    Chicago, IL
    Posts
    316
    Given:

    DB-A Table 1:
    Col. a- 1 2 3 4 5 6 7 1
    Col. b- 3 4 5 3 4 5 2 4

    It has a PK on Col. a

    Step 1:
    I create copy of table (with out data) in DB-B Table 1, PK on Col. a is enabled:

    Step 2:
    When I try to copy data from DB-A Table 1 to DB-B Table 1, the insert into DB-B Table 1 complains about uniqueness. As I have listed the values of Col. a (in DB-A), there ARE 2 rows with the value '1' - so it violates the uniqueness.

    My questions was: So why is there duplicate data in Col. a DB-A when it has a PK on it ?

    I thing the answer is: that it was probably created with 'novalidate' ???

  7. #7
    Join Date
    May 2002
    Posts
    163

    Wink

    Hello

    "Novalidate" is can not be only that reason.

    It might happen also for THE ORDER OF THE CREATION OF CONSTRAINTS ,if u have other different constraints on the same object.

    And this is the very common reason. Then we have to make disable required constraints and then revalidate that primary constraint and again reenable those disable constarint.

    Hope this will help u ....


    Thanks

    vis


    Nwcomer
    Student

  8. #8
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    find the duplicate key in db-a then

  9. #9
    Join Date
    May 2002
    Posts
    93
    Khussian,

    I think we faced the same problem. (refer to today's thread "How to find duplication key")

    It could have people create the first table without enforcng the constraints, and later they changed their mind and put constraints in it. In my case, I disabled the primary key (cascade) and copy that original table to another data source, then enabled the primary key in the original table, so it will not harm the original table, while I get the copy of it successfully.
    Hope it helps.

    [Edited by E-Quality on 08-26-2002 at 03:42 PM]

  10. #10
    Join Date
    Sep 2000
    Location
    Chicago, IL
    Posts
    316
    Here is the answer: It was a darn trigger !

    The trigger was inserting the insert time into the table on DB-B, and that is part of the PK...so....since that insert time was the same down to the seconds, it was making it non-unique:

    Thank you all for your input.

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