Insert Script fails because Foreign Keys in Shadow Database

RyanJLind

I have a very simple script that I want to include within my database project.  It inserts a record into a table.

I can deploy fine but when I try to "refresh" from the Ready Roll pane I get a SQL Server error that the script fails because of a foreign key violation.  This is obviously because the Shadow database contains no data but still contains foreign keys.

How do I get around this? I have read the articles on data population scripts but they all seem to assume you are deploying to a blank database.  Where are you supposed to put maintenance/bug-fix scripts like this? 

dnlnln

For this kind of transactional data fix, probably the best thing to do is to prevent the script from being executed against the shadow database to begin with.

This can be done by using the built-in IsShadowDatabase SqlCmd variable. For example:

<div>IF '$(IsShadowDeployment)' = 0
<span style="background-color: transparent; color: inherit; font-size: inherit; font-family: Roboto, "Helvetica Neue", Arial, sans-serif;">&nbsp; &nbsp; BEGIN
</span><span style="background-color: transparent; color: inherit; font-size: inherit; font-family: Roboto, "Helvetica Neue", Arial, sans-serif;">&nbsp; &nbsp; &nbsp; &nbsp; PRINT N'Fixing transactional data because IsShadowDeployment=0...';
</span><span style="background-color: transparent; color: inherit; font-size: inherit; font-family: Roboto, "Helvetica Neue", Arial, sans-serif;">&nbsp; &nbsp; &nbsp; &nbsp; INSERT INTO...
</span><span style="background-color: transparent; color: inherit; font-size: inherit; font-family: Roboto, "Helvetica Neue", Arial, sans-serif;">&nbsp; &nbsp; END
</span><span style="background-color: transparent; color: inherit; font-size: inherit; font-family: Roboto, "Helvetica Neue", Arial, sans-serif;">ELSE
</span><span style="background-color: transparent; color: inherit; font-size: inherit; font-family: Roboto, "Helvetica Neue", Arial, sans-serif;">&nbsp; &nbsp; BEGIN
</span><span style="background-color: transparent; color: inherit; font-size: inherit; font-family: Roboto, "Helvetica Neue", Arial, sans-serif;">&nbsp; &nbsp; &nbsp; &nbsp; PRINT N'Skipping transactional data change because IsShadowDeployment=1';
</span><span style="background-color: transparent; color: inherit; font-size: inherit; font-family: Roboto, "Helvetica Neue", Arial, sans-serif;">&nbsp; &nbsp; END
</span><span style="background-color: transparent; color: inherit; font-size: inherit; font-family: Roboto, "Helvetica Neue", Arial, sans-serif;">GO</span></div>

One caveat to note about this variable is that, in order to avoid false positives occurring in the script generation process, its use should be limited to object types that are not within the scope of the comparison engine. For example, it isn't suitable for DDL statements like CREATE/ALTER. For more information, please see Key Concepts > Script Verification. 

RyanJLind

I saw this in the documentation but I am not interested in instructing the devs that they need to wrap all of their insert scripts in this.  There really ought to be a better way.  Why bother having the foreign keys in the shadow database anyway? This is only going to lead to this problem.

dnlnln

I can appreciate where you're coming from in terms of the workaround not being an intuitive step in the script authoring process.

The only other alternative would be to seed the relevant tables referred to in the FK relationship with the required data, e.g. as part of the baseline scripts. However if you've already considered this approach, and subsequently found it to be unworkable, then I'm afraid there's no other solution that the tool offers for this kind of transactional data maintenance at the moment.

I'll take a note to log this in our issue tracking system so that the development team can consider it further. I'll post here again once there is an update.