SQL Change Automation trying to add PK constraint that already exists

michaeljmunson2

We had an issue today where the script generated by SQL Change Automation included a Unique Primary Key constraint that already existed on the Target Database, which would fail the deployment because the Primary Key already existed.

We took out the offending statement, and ran the script and did another compare (deploy) afterwards and the error went away as it didn't generate the script to try to add the existing PK constraint again...

The generated html of the changes before deploying did not show this table being modified as well, it just snuck the change in there for some reason, any manual compare wouldn't show the change either.

What could have been the problem?

Error from Octopus Deploy Attached:

Sergio R

That's unexpected, one possibility was that the release was created against a different target or that the state of the target changed between when the release was created and deployed.
However, by default, SQL Change Automation will check the target schema (Pre-Update schema check) at the start of the deployment which would prevent this.
Ultimately in order to find what's causing this we would need the nuget package and the schema of the target.
Since this issue is now resolved this might not be relevant to you anymore. However if you wish to troubleshoot this further please contact us at https://productsupport.red-gate.com/hc/en-us