CODEWITHSUNDEEP
ms-accessforeign-keysms-access-2010
panny
panny

Reputation: 2212

Relate two tables with combined fields

I want to relate the table DRG with the table DAT in 1-to-n. 

ID    Day    Month    Year    ForeignKeyDRG
1     01     April    1983    ?
2     07     June     2005    ?
..    ..     ..       ..

Table DAT

DAT stands for "DATe" and contains a list of to me relevant dates.

From    To
foo     bar
..      ..

Table DRG

Nevermind what DRG stands for, it is just an arbitrary table with values (actually it stood for "DateRanGe" but that example doesn't work out that way semantically, so let's just assume it is anything with two fields which uniquely qualify each entry.)

I don't want to introduce a third field ID in DRG, but want to have a combined key out of From and To. How do I tell MS-Access to use the combination as a foreign key in table DAT ?

Upvotes: 0

Views: 272

Answers (1)

HK1
HK1

Reputation: 12210

I don't want to introduce a third field ID in DRG

You should probably explain why you don't want a third field. Assuming that your DRG table has no other fields, the answer is you need a third field, or else you might as well just move the From To fields into the DAT table. Tables link to each other using Primary Key to Primary Key (one-to-one relationship) or else Primary Key to Foreign Key (one-to-many relationship). It is possible to use composite keys where two fields combine to create a primary key but I haven't often seen these used in relationships, although it's probably possible. In your particular case there would be nothing to be gained and in fact it would be plain ridiculous to try this because you would need From and To fields in both tables in order to form the relationship.

There are a lot of database guys that say every table should always have an auto-incrementing numeric ID field as it's primary key. I've been doing database programming off and on for several years now and I'm really starting to head that direction myself. It generally doesn't make sense to have composite keys. You can always create a unique index that includes both fields if you want to prevent having two records with the same values in both fields.

Upvotes: 1

Related Questions