I’ve come across a strange issue which looks like it could be a bug. It’s certainly inconsistent.
I’m using DA 9.4.109.1375, accessing MSSQL via FireDAC.
As an example, I have three tables, called Master, Brother and Sister, each with an Id column as primary key, defined as an IDENTITY. Brother and Sister are detail tables of Master so have a MasterId foreign key. I set these three tables up in my schema with the correct relationships defined. When I add a new Master record with descendent Brother and Sister records, the new Master gets a temporary negative Id value which is reflected in the MasterId foreign key fields in Brother and Sister. When I ApplyUpdates, these values are all correctly sorted out in the resulting database records, so the MasterId foreign key values in Brother and Sister reflect the automatically assigned value of Id in Master. This is all fine.
If, however, I add a BrotherId foreign key to the Sister table, so that a Sister record can optionally reference a Brother record, things get weird.
I configure an additional relationship in the schema to reflect this but, when I add the Master, Brother and Sister records, this time manually setting the Sister.BrotherId column value to the temporary negative value in Brother.Id, it’s only updated correctly on the server on alternate updates. Every other Sister record added will have the correct value whilst the others will have the negative one.
I’ve created a small test program which demonstrates the issue. There’s a SQL script in the ZIP file which will create the database.KeyTest.zip (22.4 KB)