Using SSDT Publish (.dacpac) to deploy non null foreign key schema updates

Question

I've been using .dacpacs to deploy database updates to our various environments. I have found a scenario that is causes the the publishing of a particular update to fail.

I need to add a new table dbo.Supplier and add a column to another table dbo.PickZone that has a non nullable foriegn key referencing the new table. The schema within the SSDT project reflects this, and to prepare for new NOT NULL column, i have the following pre-deployment script;

IF object_id('dbo.Supplier') IS NULL
BEGIN
    CREATE TABLE [dbo].[Supplier]
    (
        [SupplierId] INT IDENTITY(1,1) NOT NULL,
        [Name] varchar(50) NOT NULL,
        CONSTRAINT [PK_Supplier] PRIMARY KEY CLUSTERED ([SupplierId])
    );

    SET IDENTITY_INSERT [dbo].[Supplier] ON;
    INSERT INTO Supplier (SupplierId, Name) VALUES (1, 'Default Supplier')
    SET IDENTITY_INSERT [dbo].[Supplier] OFF;

    ALTER TABLE dbo.PickZone ADD SupplierId int NULL;

    UPDATE PickZone SET SupplierId = 1

END

The script above updates schema and data to ensure that when the publish occurs (i'm using sqlpackage.exe) that it will not fail when it applies the foriegn key constraint to dbo.PickZone.SupplierId:

CREATE TABLE [dbo].[PickZone]
(
    [PickZoneId] INT IDENTITY (1, 1) NOT NULL PRIMARY KEY, 
    [Name] VARCHAR(50) NOT NULL,
    [SupplierId] INT NOT NULL,
    CONSTRAINT [FK_PickZone_Supplier] FOREIGN KEY ([SupplierId]) REFERENCES [dbo].[Supplier] ([SupplierId])
)

The problem, is that it seems both vs2012 Publish and sqlpackage.exe deploy prepares the schema updates required then executes the pre-deployment script, and then executes the schema updates - which are now out of sync due to the pre-deployment script making schema changes.

This causes the schema publish to again attempt to add the table and column, and results in it failing.

I can obviously change my deploy process to execute these types of preparation scripts outside the of the dacpac deploy, but I would kind of like the dacpac to be responsible for all schema changes...

Does anyone know a way to get dacpac publish to cater for this type of update?

score 2 · Answer 1 · answered Jun 23 '15 at 08:47

I will describe my solution. We going to use sqlpackage tool which deploys dacpacs. This tool supports a lot of parameters. One of them is GenerateSmartDefaults. See here.

As I think pre-deployment script not must contain schema modifications. Only SELECT, INSERT, UPDATE, DELETE statements. Post-deployment script too.

Modify your db schema (add not null column without default or as foreign key).
In post-deployment script add UPDATE statement for this column and build dacpac.
Deploy this dacpac with SqlPackage tool like this: sqlpackage.exe /Action:Publish /SourceFile:your.dacpac /TargetServerName:serverName /TargetDatabaseName:databaseName /p:GenerateSmartDefaults=True

score 1 · Answer 2 · answered May 13 '13 at 16:47

1

I think you're trying to do this in the wrong order. By default, SSDT won't check the constraints until after the entire update is done - even after the post-deploy scripts. That means that you should be able to just add the table in your project with the default and constraint, issue a data insert/update in a Post-Deploy script, and let SSDT enable the FK constraint after everything is done.

If you're not comfortable with that, you can always do it in this order:

Create the table
Populate the table in a post-deploy script
Snapshot that schema
-----------------
Add the column (with default) and FK Constraint
Snapshot that schema
-----------------
Publish Snapshot A
Publish Snapshot B

I don't think you have to go through the second process in most cases if your options are set correctly to enable checking constraints after everything else.

answered May 13 '13 at 16:47

Peter Schott

4,521
21
30

Thanks, i'll give running the insert/updates in the Post-Deploy a shot. Although the end of this (old) thread notes that it doesn't work: http://social.msdn.microsoft.com/Forums/en-US/ssdt/thread/05e195cb-2005-4dcf-84db-705b22972dc8 – Stafford Williams May 14 '13 at 04:35
If this is an infrequent occurrence, I'd recommend generating the script and editing it. However, I think that you should be able to use a combination of smart defaults (or an actual default value) with the FK constraints not being enabled to allow you to set these through the project. The thread seemed to point to multiple changes being done that were all interdependent. The "Script validation for New Constraints" option should help. I just did a quick check w/ AW2012 and it created a NOCHECK FK, then altered to CHECK after post-deploy scripts. – Peter Schott May 14 '13 at 14:10
I had the same problem and this solution worked for me. If you call sqlpackage.exe /action:script ..... then you can see the script that it will run. In VS2013 it adds the FK "with nocheck" runs the post deployment script and then alters the FK but "WITH CHECK" as indicated by Peter – Amjid Qureshi Feb 19 '14 at 20:16

Using SSDT Publish (.dacpac) to deploy non null foreign key schema updates

2 Answers2