Updating multiple Data tables - generate script incorrect

TheoL

I have added a number of tables as Data Tables to my SCA project in Visual Studio 2017.  I and using release 4.2.20176.

I added data to two tables, one of them a FK reference to the other.

The problem I encountered was that the update scripts were generated out of order, so the dependent data changes were applied before the required reference data was created.  Naturally, the update script failed to validate.  I had to manually correct the order of the update commands.

Should this not work automatically?  Since the FK relationship is defined for the updated data tables, I expected the dependency to be worked out, and the insert / updates created in the correct order.  (Of course dropping the FK's - update - re-apply FK's would also work - but this is not desirable in some cases).  

Am I expecting too much, or have s stumbled upon a bug?  

Details of the scenario:


Data updates is in tables Data1 and Data1 (highlighted) where new and updated records in Data1 are referenced by changes in Data2.
In detail:
1 new record in Data1
1 changed record in Data1 (field [Data])
Multiple changes in Data2 to reference either the new or modified records in Data1.
The SCA generated change script applied changes to Data2 first before applying the changes to Data1

Sergio R

In those cases you can use the Comparison Option to Disable Foreign Keys: https://documentation.red-gate.com/sca/developing-databases/concepts/advanced-concepts/comparison-and-script-generation-options

TheoL

Thanks for the work-around.   In my case, data sets are small, and therefore removing and attaching the FK is of little consequence. And I guess this would would generally be true for any data that has been included as Data Tables in SCA

I do see it as a work-around - since the automatically generated script generated  by SCA did not work, and failed it's own verification.

I consider this a fairly trivial case.

Data2 depends on Data1. (Directly in this case with a FK relationship).
Data1 has no dependencies on Data2.
=> Generate new Data1 records before applying updates to Data2

I was not expecting this to be beyond the compare engine used by SCA.