How can I prevent SqlPackage.exe from dropping and re-creating constraints when deploying a dacpac?

Question

I have a Visual Studio sql project with a table defined like the following:

CREATE TABLE [dbo].[Hoerses]
(
   [HoersId] INT NOT NULL PRIMARY KEY,
   [DatePurchased] datetime NOT NULL CONSTRAINT [DF_Hoerses_DatePurchased] DEFAULT DATETIMEFROMPARTS(1985,01,01,0,0,0,0)
)

When I target a preexisting SQL database with a "Script" command

sqlpackage.exe /Action:Script /SourceFile:DatabaseProject1.dacpac  /Profile:publish.xml /OutputPath:deployscript_test.sql /TargetPassword:redacted

Then I get the following generated SQL even though the constraint had the same name and definition before & after:

PRINT N'Dropping [dbo].[DF_Hoerses_DatePurchased]...';


GO
ALTER TABLE [dbo].[Hoerses] DROP CONSTRAINT [DF_Hoerses_DatePurchased];


GO
PRINT N'Creating [dbo].[DF_Hoerses_DatePurchased]...';


GO
ALTER TABLE [dbo].[Hoerses]
    ADD CONSTRAINT [DF_Hoerses_DatePurchased] DEFAULT DATETIMEFROMPARTS(1985,01,01,0,0,0,0) FOR [DatePurchased];


GO
PRINT N'Update complete.';


GO

(My main concern with trying to prevent this superfluous re-creation is because I occasionally see a "Lock request time out period exceeded." error when it's trying to drop a constraint during actual deployments/publishing)

score 4 · Accepted Answer · answered Dec 16 '15 at 21:14

The problem was apparently in the use of DATETIMEFROMPARTS.

If I instead declare the table as

CREATE TABLE [dbo].[Hoerses]
(
   [HoersId] INT NOT NULL PRIMARY KEY,
   [DatePurchased] datetime NOT NULL CONSTRAINT [DF_Hoerses_DatePurchased] DEFAULT '1985-01-01'
)

Then SqlPackage.exe no longer tries to drop & re-add the constraint.

score 2 · Answer 2 · answered Mar 07 '17 at 10:55

Dacpac deployment works by comparing an XML construct of the Database schema with one from your VS project. Sometimes this process is confused by syntactic niceties resulting in repeating changes such as you describe. My recommendation is that you go to a deployed database, script out the troublesome object and paste that back over the implementation your Visual Studio file for that object. This resolves the apparent discrepancy so the deployment is no longer fooled into believing it has a change to implement.

mihails.kuzmins · Answer 3 · 2020-06-13T14:28:41.943

I have also found an article which helped me. http://johnnydba.blogspot.com/2015/07/are-your-vs-database-projects-dropping.html

Basically these are the possible issues mentioned in the article:

Some system functions are represented as lowercase in SQL Server like getdate(), sysutcdatetime(), getutcdate(), and newid().

myDate datetime2 DF_myTable_myDate DEFAULT(getdate()) NOT NULL

Scalar numerical values in default constraints need to be enclosed in double parenthesis ((0)), but I noticed that string values do not.

myBit bit DF_myTable_myBit DEFAULT((0)) NOT NULL

Filtered index and check constraint conditions must be enclosed in parenthesis (), the column names must be in square brackets, and the comparison operator must not have spaces between the left and right side.

-- Column check
CONSTRAINT CH_myTable_someCheck CHECK ([myColumn]>(0))
-- Function check
CONSTRAINT CH_myTable_anotherCheck CHECK ([dbo].[someFunc]([myColumn],[myAnotherColumn])=(1))

Some columns must be enclosed in square brackets []

Thank you. My index needed ALL of 2,3 & 4! – Brondahl Jan 14 '21 at 18:59 — Brondahl, Jan 14 '21 at 18:59

score 0 · Answer 4 · answered Mar 11 '22 at 21:40

It's the keyword case used for the constraints.

Use lowercase for any keyword like getdate() or newid() in your defaults.

These constraints will be always dropped:

CREATE TABLE [dbo].[APPLICATION_ERROR]

(
    [ERROR_ID]      UNIQUEIDENTIFIER
        CONSTRAINT [CT_ERROR_ID] DEFAULT (NEWID()) NOT NULL,
    [ERROR_DATE]    DATETIME
        CONSTRAINT [CT_ERROR_DATE] DEFAULT (GETDATE()) NULL
);

these will not:

CREATE TABLE [dbo].[APPLICATION_ERROR]

(
    [ERROR_ID]      UNIQUEIDENTIFIER
        CONSTRAINT [CT_ERROR_ID] DEFAULT (newid()) NOT NULL,
    [ERROR_DATE]    DATETIME
        CONSTRAINT [CT_ERROR_DATE] DEFAULT (getdate()) NULL
);

If the constraint is numeric, you should use double parenthesis:

   [LABOR_AMOUNT] MONEY
     CONSTRAINT [DF_LABOR_AMOUNT]
                DEFAULT ((0)) NULL

You should also add this to your publishing profile, but it's not the core of the issue:

<IgnoreKeywordCasing>True</IgnoreKeywordCasing>

How can I prevent SqlPackage.exe from dropping and re-creating constraints when deploying a dacpac?

4 Answers4