DTS conversion stopped due to Foreign Key constraints 1

[Catadmin]

I have a DTS job that I'm running that keeps failing because of Foreign Key contraints on a different table.

My DTS job deletes the rows previously in Table1 and re-inserts them (or tries) because the old records get updated regularly. Table2 has a foreign key constraint that relates back to two of the columns on Table1. Table2 can't be updated until after Table1 gets reset. Unfortunately, Table1 can't get reset due to the Foreign Key constraints.

The only option I've been able to figure out is to delete the foreign key constraints on Table2 that reference back to Table1. Does anyone have any advice on another option I could check on?

I'm not sure my boss will be too terribly happy about the FK-C deletion. However, the DB this job is going to is going to end up read-only. So, I'm thinking the FK-Cs being deleted won't be that big a deal.





Catadmin - MCDBA, MCSA
Remember, "Work" is a four letter word. And you know what your mother told you about using four letter words!

 

[mrdenny]

Your options are:

1. Remove the FK before processing.
2. Do updates, not delete/insert.
3. Disable the FK completly.
4. Delete the data from table2 and reload after you reload table 1.

My order of preference would be 2, 4, 1, 3

Denny

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

(My very old site)

 

[Catadmin]

Thanks, Denny.

#2 won't be possible because of old records getting updated on the original table and, due to haphazard DB design (before my time), my update query spends 2 hrs. + just on the table scan looking to see if the record already exists in the new table.

But I'll check into the others.



Catadmin - MCDBA, MCSA
Remember, "Work" is a four letter word. And you know what your mother told you about using four letter words!

 

[mrdenny]

Another possible option, we'll call it number 5

Move the data from table2 to table2_temp.
delete the data from table1
move the data from table2_temp to table2.

Denny

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

(My very old site)

 

[Catadmin]

<blinkblink>

Now that one is so obvious that I should have thought about it. DOH!

Thanks, Denny. You're a gem!



Catadmin - MCDBA, MCSA
Remember, "Work" is a four letter word. And you know what your mother told you about using four letter words!

 

[mrdenny]

no problem.

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

(My very old site)