I sometimes see this when deploying via sqlpackager.exe:

Analyzing deployment plan (Complete) Updating database (Start) Dropping DF_XXX... Creating DF_XXX... Update complete. Updating database (Complete) Successfully published database.

This happens everytime I publish, even though there have not been any changes.

Typically this is caused by Sql taking the create statement from SSDT and changing it, it does things like changes GETDATE to getdate and (0) to ((0)), to be honest I am not 100% sure what it will and won’t change but my fix for this when I get it happening is to go to SSMS and script out the table and copy and paste the constraint back into SSDT, then deploy and it should stop.

An example is the extra parenthesis which get added,so if you take:

CREATE TABLE xxx ( id int not null, status int not null default (0));

When you deploy to Sql this ends up as:

CREATE TABLE xxx ( id int not null, status int not null default ((0)));

Who knows why it does this but it does, just remember script it back from SSMS and forget about it!

---

Comments:

Nigel Ainscoe

January 18, 2015 - 10:03

Fix for this

My fix for this is always to re-sync the database back to the model after a deployment.

Ed Elliott

January 18, 2015 - 20:52

Cool - do you ever get any

Cool - do you ever get any issues with it?

Todd edwards

July 17, 2015 - 22:22

named constraints

Use named constraints. If you don’t specify a name, a system generated name is created like DF_ACTI_153af. That name is created during the publish and does not exist in your project. So, on the next publish, the compare cannot match the constraint in the target to the source and assuming your drop objects in target =true, the constraint with the system name is dropped because it does not match the project, then the constraint is added now with a different system generated name. We had exactly this issue, we added names to all of the constraints then they stopped getting dropped and added.

Ed Elliott

July 22, 2015 - 08:40

Hi,

Hi,

Great advice, naming constraints really is a must when you automate deployments even without SSDT I would always do it.

ed

mikeasdf

December 16, 2015 - 20:13

Even when using a consistent

Even when using a consistent constraint name, I’m still seeing sqlpackage.exe dropping and recreating the constraint.

mikeasdf

December 16, 2015 - 21:49

nevermind

Apparently my usage of DATETIMEFROMPARTS in the constraint value was somehow causing the unnecessary drop. Changing to a quoted date constant helped prevent it.