optional relationship -- referential integrity?

[Jerry Whittle]

What is the primary key of the Meter table? That field should be the foreign
key in the Connection table. This will only work if a Connection requires one
and only one Meter. If a Connection does not require a Meter or can have more
than one Meter, then you need a third table to bridge or link the Meter and
Connection table.

I'm also assuming that a Connection can only have one Premise.

 

[AndyK]

Thanks for the response Jerry. I think I now understand - my fundamental
problem is that I only have one part of the tblMeter composite primary key as
the foreign key in tblConnection. Unfortunately this is the way the data is
presented, although I could massage the data first in temp tables before
loading to the tables with referential integrity set.

Thanks again for your help.

 

[AndyK]

hi all - thanks for reading.

I have two tables in an Access db: tblMeter and tblConnection.

tblMeter has a composite key of MeterRef + PremiseRef; both these fields are
required, and indexing for both is set to "Duplicates OK"

tblConnection includes MeterRef as a foreign key; the field is required and
indexing is set to "No Duplicates"

All of the tblConnection.MeterRef data will exist in tblMeter.MeterRef;
however, not all of the data in tblMeter.MeterRef will have an equivalent in
tblConnection.MeterRef. In other words this as a one-to-one relationship,
optional at the tblConnection end - a tblMeter can relate to none or 1
tblConnection, a tblConnection relates to one and only one tblMeter

My problem is - I can't find a way of enforcing referential integraity for
the relationship between the 2 MeterRef fields - and I don't want to combine
the data into a single table.

Any help would be greatly appreciated!!

 

[Tim Ferguson]

tblMeter has a composite key of MeterRef + PremiseRef; both these
fields are required, and indexing for both is set to "Duplicates OK"

tblConnection includes MeterRef as a foreign key; the field is
required and indexing is set to "No Duplicates"

This whole thing adds up to a one-to-many relationship, with each
Connection having several Meters... is this what you intended?


Tim F