3

I am trying to publish a .dacpac extract to a target database, I want to exclude just the foreign key constraints in all the tables. After going through the ExcludeObjectTypes options I do not find a clear way to exclude just the foreign keys.

I need to migrate the schema structure of the source except for the foreign keys as I will be loading the data in-parallel by other means. So the requirement is to just exclude the foreign keys when I perform Publish

3
  • I don't think you can. Is your concern that there may be foreign keys in your source that you do not want deploying - or the other way around? There may be ways around what you are trying to achieve with the options available. – Matthew McGiffen Sep 17 '19 at 13:16
  • Thanks for response @MatthewMcGiffen . I need to migrate the schema structure of the source except for the foreign keys as I will be loading the data in-parallel by other means. So the requirement is to just exclude the foreign keys when I perform Publish – Shikhar Nahar Sep 17 '19 at 13:20
  • 1
    As it doesn't look like you can exclude the foreign keys, what you may want to do is disable the constraints before your data load and then re-enable them again afterwards. Here's an example of that: stackoverflow.com/questions/159038/… If you are just loading specific tables - then just to disable to ones you need. – Matthew McGiffen Sep 17 '19 at 15:54
1

You need to disable deployment option "Script validation for new constraints".
Foreign keys will be still created, but in not trusted state and existing data will not be validated.
Publish script will contains something like:

ALTER TABLE [Tab] WITH NOCHECK
    ADD CONSTRAINT [FK_Tab] FOREIGN KEY ([Id]) REFERENCES [Tab2] ([Id]);

This will allow you to deploy all constraints without errors, but you still need to disable them in post deployment script.

enter image description here

0

If you have control over the creation of the dacpac file, your best bet would be to publish normally, and then use a post deploy script that drops all of the foreign key constraints in that database.

A post deployment script is a SQL script in your SSDT project that has a "Build Action" of "PostDeploy" like this:

screenshot of post deploy script in Visual Studio

There are lots of options for dropping all foreign key constraints using a T-SQL script, many of which are discussed in this Stack Overflow Q&A: How to drop all Foreign Key constraints in all tables?

1
  • 2
    Seems like there isn't a way to avoid publish from creating the foreign key. Will work around by disabling it when loading data. Thanks – Shikhar Nahar Sep 17 '19 at 17:13
0

Here's a possible solution if you're happy waiting a long time:

Sqlpackage.exe to support extracting table data WITHOUT all associated foreign key tables

For some background, the rationale of the existing limitation is explained here

The reason behind the design choice was this:

A dacpac can be published to any database -- empty or non-empty. And when table data is published, if the target table isn't empty, all of the data is dropped and replaced. But if the target table isn't empty and it has incoming FK dependencies, then there could possibly be broken FK references. Requiring that all tables be a closed set resolves this problem, because any incoming references will be erased as well.

This isn't the only design decision that could have been made. For example, we could modify this logic to allow incoming FKs from tables that aren't exported, and then perform a pre-deployment check to verify that there will be no incoming FK reference issues.

For my purpose, I'm trying to create a seed database for developers to use setting up thier local environments, this means I'm deploying to empty databases so the FK's don't need to be a 'closed set'. I only want some lightweight reference tables populated.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.