Strange problem with foreign keys

delphi

(tobygroves) #1

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)


(EvgenyK) #2

Hi,

create master relation for Sister table as

    DetailFields = 'MasterId;BrotherId'
    MasterFields = 'MasterId;Id'
    MasterMappingMode = mmWhere
    MasterSource = ds_dbo_Brother

it will solve issue when reference to brother is present.

Note: you can use cloned tables feature and create cloned table so it will cover both cases:
when sister has brother (mastersource = brothers) and when it hasn’t (mastersource = master)


(tobygroves) #3

Ok I might try that.

To be honest it’s probably easier to just apply updates before setting Sister.BrotherId, then set it afterward, once the correct Id has been retrieved, and apply updates again.


(EvgenyK) #4

Just specify update rules in Schema:
1st rule - master
2nd rule - brother
3rd rule - sister

it will solve case when sister table was processed before brother table


(tobygroves) #5

Ah of course, that fixes it completely.

I presume, without the update rules, it was alternately processing brother and sister in the opposite order each time, for some strange reason. If brother was processed first, it worked, if not, it failed.


(EvgenyK) #6

probably using of generics in our code causes this behavior