Incorrect export of multi-field FOREIGN KEY/REFERENCES Relationships

[ewenmcneill]

JET 4 (or at least recent Access versions) allow the creation of multiple-field relationships between two tables (eg, foo (a, b) references bar (c,d)). This appears to be stored in MSysRelationships as multiple rows with the same szRelationship, and ccolumn greater than 1 (eg, 2), and icolumn between 0 and ccolumn - 1, for the individual parts. These multiple fields need to be combined together into one relationship that involves the multiple columns on each side.

mdbtools at present just outputs each row (relating to the table it is processing) individually, without looking at ccolumn or icolumn, which results in two single-field FOREIGN KEY references -- neither of which is likely to work (because the referenced fields are unlikely to be unique individually if there was a need for a multi-field FOREIGN KEY reference).

To properly support this, mdb_get_relationships() would need to check ccolumn and if it is greater than 1, then scan for all the icolumn values (in order) with the same szRelationship, before building the resulting FOREIGN KEY/REFERENCES statement. (And then later skip over any icolumn != 1 values when processing later rows.)

I had only one of these to deal with so far, so ended up fixing it up with the sed on my schema export rather than trying to implement this in C now. But I wanted to record the issue for later reference (and as search bait).

For reference, schema of MSysRelationships in JET 4 (modern Access) database:

CREATE TABLE [MSysRelationships]
(
    [szRelationship]            Text (510), 
    [grbit]         Long Integer, 
    [ccolumn]           Long Integer, 
    [icolumn]           Long Integer, 
    [szObject]          Text (510), 
    [szColumn]          Text (510), 
    [szReferencedObject]            Text (510), 
    [szReferencedColumn]            Text (510)
);

Ewen

[ewenmcneill]

Closing issue (which I created), to clean up my "created issues" list, because it seems unlikely to be fixed after 3.5 years, particularly since it looks like the code base is unchanged in the last couple of years. I've ended up with a few more sed work arounds in the only case where a client database conversion was running into this, and that's sufficient for where I care about it.

Ewen

[evanmiller]

MDB Tools is open for business. Re-opening issue.

[ewenmcneill]

As noted in the other PR I've no longer got an interest in this code/issue (the client importing MDB database stopped a few years ago), so I'm going to unwatch this issue now.

FWIW, in passing, it seemed like multi-column foreign key references were increasingly common towards the end of the point that I was helping my client to import MDB databases. So there may well be others running into this same issue (I kludged around it, with sed/awk rewrites of the schema, until we stopped needing to import MDB databases.

Ewen

PS: unwatching this issue too.

[evanmiller]

@ewenmcneill Duly noted, leaving open in case others hit the same issue. Thanks for your time.