unique contraint issue

[khussain]

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 ?

[pando]

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

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

of coz you get unique constraint error

[Fiza15]

You are not allowed to have duplicate values...coz of Primary key

[khussain]

I know that !!

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

[pando]

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

[khussain]

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' ???

[newcomer]

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

[pando]

find the duplicate key in db-a then

[E-Quality]

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]

[khussain]

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.