If you write code to SQL Server then you might be interested in this: I have written a tSQLt tdd training course which has helped over 500 people learn both tSQLt and how to apply TDD practices to their SQL Server T-SQL development, you can join the course at https://courses.agilesql.club.

Why does SqlPackage keep dropping and recreating constraints?

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!


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



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



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.


December 16, 2015 - 21:49


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


* indicates required

Please select all the ways you would like to hear from Agile Sql Club:

You can unsubscribe at any time by clicking the link in the footer of our emails. For information about our privacy practices, please visit our website.

We use Mailchimp as our marketing platform. By clicking below to subscribe, you acknowledge that your information will be transferred to Mailchimp for processing. Learn more about Mailchimp's privacy practices here.