What''s up with table dependencies??? Insert Statement Conflict with Table Foreign Key Constraint

Question

What''s up with table dependencies??? Insert Statement Conflict with Table Foreign Key Constraint

Robert Wetherill

Right there with Babe

Points: 790
More actions
September 20, 2005 at 8:38 am

#66700

I generated an SQL Script to create an exact copy of a database. I then tried using DTS to transfer the data, and I get "Insert Statement Conflict with Table Foreign Key Constraint yada yada yada
This database was created by some other guy who is not here and he has freakin' table dependencies all over the place. Where can I learn about dependencies and how can I get around this? (Maybe add the dependencies later after DTS?)

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

Greg Charles SSC-Forever Points: 45403 More actions · Answer 1

What you do is run a script in Query Analyzer to generate ALTER TABLE DROP CONSTRAINT and ALTER TABLE ADD CONTRAINT statements for the foreign keys. Copy the statements into Execute SQL tasks in your DTS package - DROPs before the Transform Data task and ADDs after.

I posted the FK script previously in this thread: http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=5&messageid=144674

Greg

Robert Wetherill Right there with Babe Points: 790 More actions · Answer 2

Robert Wetherill

Right there with Babe

Points: 790

September 20, 2005 at 9:15 am

#591372

Thanks that gives me what I need to know.

Bob

Robert Wetherill Right there with Babe Points: 790 More actions · Answer 3

Robert Wetherill

Right there with Babe

Points: 790

September 20, 2005 at 9:20 am

#591375

How do I mark this post at satisfied???

Greg Charles SSC-Forever Points: 45403 More actions · Answer 4

I don't think this forum software has that option. You could post it in the suggestions forum.

Greg

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 5

It's already been suggested. Would be too much work to code what they needed and maintain the site that way (point system and all). So just say that the solution worked and that'll be more than most do .

Robert Wetherill Right there with Babe Points: 790 More actions · Answer 6

Thanks! Just trying to do my part in being a responsible professional!!!

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 7

Thank you for that... but I don't want you to waste anytime on something that won't be done .

Scott Coleman One Orange Chip Points: 27452 More actions · Answer 8

"Where can I learn about dependencies?"

Create a database diagram and add the tables in question. There is an option to add related tables. Select all the tables and use "Add related tables", repeat until no more new tables show up. (Or just select all the tables in the first place.)

After arranging the tables into something useful, it will show all the table relationships. A word of caution, the database diagram view is an active design tool. If you add or delete any relationship arrows, the changes will be made in the database when the diagram is saved. Also, "Remove table from diagram" and "Delete table from database" are two different actions.

Robert Wetherill Right there with Babe Points: 790 More actions · Answer 9

Thanks.

Is it a good strategy to use dependencies or best to leave these empty and manage data integrity with the middleware solution?

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 10

The closer to the data you do this, the better. If you use dependencies then noone can enter bad data (assuming everything is correctly validated). But even with a middleware, a programmer could run an update statement and screw something up (intentionally or not).

Robert Wetherill Right there with Babe Points: 790 More actions · Answer 11

Thanks. Oracle JD Edwards running on SQL Server does not use ANY depencencies in ANY of their tables. So the door is wide open to do damage if you have update capability and don't know what you are doing.