2

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

  • 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 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 at 13:20
  • 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 at 15:54
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
    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 at 17:13
0

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

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.