Why does SqlPackage keep dropping and recreating constraints?


Learn how to use tSQLt to professionally unit test T-SQL code. To enroll in the free self-paced course visit here

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!


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

Cool - do you ever get any issues with it?

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.


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


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

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


Add new comment

Site Search with Duck Duck Go