How to fix issue with the sequencing of static data and the migration script that references static

Facing an issue in the TFS build - SQL Change Automation build step.

It fails mentioning that my migration script has insert statement which is using the data that's not existing - basically it is a foreign key violation/referential integrity issue. In my migration script I have an insert statement in which a column's value is dependent on the lookup data which is added into static data.

Removing the linked static data and adding it as migration script before my actual migration script fixes this issue - however I don't want to go in that route since I feel that adding static data into migration script sounds weird and what is the use of linking static data feature here if it doesn't help in anyway ( I could be wrong here)

Any alternatives to get around and fix this issue?
Tagged:

Answers

  • Sergio RSergio R Posts: 600 Rose Gold 5
    I believe that you already have a Support ticket open, so we will give you a more targeted answer there.
    As part of the Schema Validation process, SQL Change Automation attempts to validate your static data, so if you have static data set for the child table but not for the parent table in a FK relationship you can run into issues.
    You could try using the "SkipFKChecks" SQL Data Compare option on the Build step or you could create the Parent table's schema and data on a Pre-Deployment script (you need to combine this with a filter that excludes the Parent table on the Build step, otherwise SCA will attempt to deploy it twice).

    Kind Regards,
    Sergio
    Product Support Engineer
    Redgate Software Ltd
    Please see our Help Center for detailed guides on how to use our tools
Sign In or Register to comment.