I have a scenario in which these changes happened at the same pull request to Git:
1. Added a new row to static data table ‘A’
2. Added a new column in non-static data table ‘B’, with foreign key to table ‘A’
3. Assigned the value of the new row in ‘A’ to the new column of an existing row in table ‘B’.
When pulling down the changes in another database via SQL Source Control + SSMS, the changes came across smoothly, no issue.
In SQL Change Automation however, the process tried to execute (2 + 3) before (1), resulting in a foreign key error.
The setup, performed on Azure DevOps, was to:
1. (Pipeline) Build an SQL Source Control project and publish the nuget package
2. (Release) Take the nuget package and execute ‘Deploy Database Changes from a Build artifact’
The situation is common enough that I believe it happens all the time without incident but this time it's tripped us up and we can't figure out why. When we tried pre-adding the foreign key to target database before re-running the deployment, this passed correctly. Was the FK being new something that tripped up the sequence? Why does this not happen in SSMS's implementation then?
I have a scenario in which these changes happened at the same pull request to Git:
1. Added a new row to static data table ‘A’
2. Added a new column in non-static data table ‘B’, with foreign key to table ‘A’
3. Assigned the value of the new row in ‘A’ to the new column of an existing row in table ‘B’.
When pulling down the changes in another database via SQL Source Control + SSMS, the changes came across smoothly, no issue.
In SQL Change Automation however, the process tried to execute (2 + 3) before (1), resulting in a foreign key error.
The setup, performed on Azure DevOps, was to:
1. (Pipeline) Build an SQL Source Control project and publish the nuget package
2. (Release) Take the nuget package and execute ‘Deploy Database Changes from a Build artifact’
The situation is common enough that I believe it happens all the time without incident but this time it's tripped us up and we can't figure out why. When we tried pre-adding the foreign key to target database before re-running the deployment, this passed correctly. Was the FK being new something that tripped up the sequence? Why does this not happen in SSMS's implementation then?