SSDT V11.1.50730.0 Using Schema compare to make changes from my development environment to a test environment (a copy of Production).

Part of the changes is the addition of columns to an existing table [Items]. The script that SSDT creates first drops foreign keys, creates and populates a temp [Items] table, then attempts to drop [Items] table. This produces the error:

Could not drop object 'dbo.Items' because it is referenced by a FOREIGN KEY constraint.

Is there some setting or reason that would result in the script generator to not include dropping and later recreating some of the foreign key constraints? Just wondering if it is something simple before I start manually modifying the script.

  • 1
    Is the model update to date in the dacpac? Maybe these are FKs that it doesn't know about? I can't think of an option that would do just "some". Usually it is all or none. Though, to be honest, I would never rely on such methods for migrating schema changes in the first place. I am a huge proponent of having targeted changes in a script, applying that script to dev, and once everything works, apply that script again to Testing / UAT. Once that is all settled, you now have a good rollout script for production that has been tested :-). Dropping a table to add columns? How many millions of rows? – Solomon Rutzky Jan 19 '16 at 20:32
  • 1
    @srutzky thanks for the feedback...FYI, I am a developer by trade, not a DBA, so I know just enough to be dangerous. I use SSDT from within Visual Studio to auto generate the schema changes script. This table has several new columns added and the approach it takes is to create a temp table, insert into it from the original table, then dropping the original table, then renaming the temp to the original table name. There is not that many rows in the table, so it doesn't take long to execute. Not sure why it uses that approach. Maybe because those new columns have default values. – crichavin Jan 19 '16 at 21:13
  • Ok. Understood about level of expertise with regards to writing these scripts by hand ;-). But, have you added any FKs directly to the DB without first going through SSDT? I am wondering if there are FKs that it doesn't know about, or if it auto-discovers each time. Can you manually drop the FK(s) that are not scripted for dropping? I am wondering if there is a reason that they cannot be dropped. – Solomon Rutzky Jan 19 '16 at 21:40
  • @ChadRichardson Are you changing the order or columns in Items table? If so, SSDT needs to recreate the table and so FK constraint. You can avoid recreation of table and FK constraint by adding the new column at the end – Ashwini Mohan Sep 26 '16 at 3:39

check under Tools->options->SQL Server Tools->Data compare if "Disable foreign keys" is checkedenter image description here

  • Life saver! In my case I needed to drop the unique indexes to prevent data sync problems and your instructions showed me how. Thanks a lot! – Maarten Kieft Apr 3 '18 at 9:59

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.