Deployment script always re-create constraints and indexes

Question

When generating a deployment script from SSDT it always generates code that drops the foreign key constraints & indexes then creates again those constraints and indexes. How to remove the code that drops and recreates those objects when generating deployment script? Any options in SSDT?

Answer 1

It is normally because there is a difference between how they are written and how sql stores them.

As a one time thing to a compare back from the db to the project and you should see some difference on the keys, apply them and it should stop doing it.

Answer 2

As the previous answer stated, it will drop and recreate objects in many situations where it does not need to. Your best bet is updating either the database or the source control definition for the objects to be in sync with each other, and not rely on the SSDT reconciliation tool, which makes very bad choices.

For example, if there is a difference in a constraint name, for example, between source control and the database environment, you could rename it in the database environment to be consistent with SC, or rename it in SC to be consistent with the DB. Otherwise the SSDT will want to drop and recreate it because it always takes the path that will work in all situations, not the path of least resistance.

I've seen it drop and recreate a table just to change the column definition on one column from VARCHAR(50) to VARCHAR(100).