1

I am using an EF code first generated database which provides some seed data, and imported data from other databases (ten thousands of rows, so it doesn't make sense to seed in EF). In an effort to streamline the process I figured I could just script the entire database's data only. So if I ever need to DropCreate (right now we use Migration strategy) my database again I can seed all my data at once.

However, the script generated by SQL Server causes tons of these conflicts:

The INSERT statement conflicted with the FOREIGN KEY constraint...

I can imagine this is because our data is very complex and inserting the data in the wrong order can cause this. Is there some way I can get the .sql script to ignore/turn off all foreign key constraints for the database before populating the tables, but then turn them back on after insertion is complete? There should be no constraint issues once all the data is inserted.

SventoryMang
  • 10,275
  • 15
  • 70
  • 113
  • 2
    See this you can temporary disable the constrains. http://stackoverflow.com/questions/159038/can-foreign-key-constraints-be-temporarily-disabled-using-t-sql – Reza Aug 14 '12 at 15:32

1 Answers1

1

Yes, you can temporarily disable foreign keys :

ALTER TABLE MyTable NOCHECK CONSTRAINT FK_MyTable_MyKey

When you reenable the constraint, you should check the constraints to ensure there are no violations, like so (Note the double CHECK CHECK)

ALTER TABLE [MyTable] WITH CHECK CHECK CONSTRAINT FK_MyTable_MyKey

See this post for a database-level solution.

Community
  • 1
  • 1
StuartLC
  • 104,537
  • 17
  • 209
  • 285
  • Is there some way to do this at a database level instead of per key? Doing that line for every single key is going to take a long time and could change depending on if my tables change. It's going to suck maintaining that hten. – SventoryMang Aug 14 '12 at 15:34
  • Nm, I see Reza's comment points to it. http://stackoverflow.com/questions/159038/can-foreign-key-constraints-be-temporarily-disabled-using-t-sql Can you edit that into your answer so when I mark it as accepted it's got everything in it? – SventoryMang Aug 14 '12 at 15:35