multiple-field relationships and null values

[peter zinniker]

Consider a database structure like this:
table1:
field1, primary key

table2:
field1
field2

table3:
field1, primary key
field2, primary key

table4:
field1
field2
fieldX

referencies having reverential integrity:
a) field1 in table1 to field1 in table2, table3 and table4
(single-field relations)
b) [field1,field2] in table2 to [field1,field2] in table3
(multiple-field relation)

So far so good. I can have records in table2 that have
null values in field1, i.e. that have no relation to
table1. But I can't have records in table4 that have null
values in field2, i.e. have relation to table1 but no
relation to table3. In other words, it works with single-
field relations but not with multiple-field relations.
Why? Does somebody know a solution?
By the way: this was possible with Access 2, but no more
with Access 97 and later versions.

 

[Tim Ferguson]

Peter

You have only given us a partial schema, so I have to assume that there are
unlisted fields Table2.FieldPK2 and Table4.FieldPK4, which are the Primary
Keys for their respective tables.

I also note that the relationship Table2.Field1 References Table1.FieldI is
redundant, because T2.F1 values have to exist in T3.F1, and that column
itself is constrained to be valid in T1.F1 as part of the multifield
relationship. Still, it's not doing any harm, as far as I can see.

So far so good. I can have records in table2 that have
null values in field1, i.e. that have no relation to
table1.

Only if the T2.F2 is null as well, because otherwise the T3-T2 relationship
will be violated. Which is what you would expect.

But I can't have records in table4 that have null
values in field2, i.e. have relation to table1 but no
relation to table3.

The only constraint you have described on Table4 is the Field1 References
Table1. According to what you have posted, you can stick any garbage you
like in Table4.Field2 because it is not related to anything.

More information please? And posting the actual DDL would help.


B Wishes


Tim F

 

[TC]

(snip)

table3:
field1, primary key
field2, primary key

This is perhaps better described as:
table3:
field1 ( composite )
field2 ( primary key )
Otherwise you may mislead people into thinking that you are trying to have
two seperate primary keys.

referencies having reverential integrity:

I suggest using referential integrity instead!



TC

 

[Peter Zinniker]

Sorry Tim,
this was an stupid error on my side in describing the
structure: reference b) is from table3 to table4, of
course. Thank you for your comments.
Here the whole thing again:
table1:
field1, primary key

table2:
field1
field2

table3:
field1, primary key
field2, primary key

table4:
field1
field2
fieldX

referencies having referential integrity:
a) field1 in table1 to field1 in table2, table3 and table4
(single-field relations)
b) [field1,field2] in table3 to [field1,field2] in table4
(multiple-field relation)

 

[Tim Ferguson]

this was an stupid error on my side in describing the
structure: reference b) is from table3 to table4, of
course. Thank you for your comments.

This is symmetrically exactly the same as the first thing you posted.

I have to admit that I really don't see the point, or what you think is not
working. There are still bits missing from the schema, as I mentioned.
Perhaps if you explained what you are really trying to do, rather than
abstracting it?

B Wishes


Tim F

 

[Peter Zinniker]

Uf, not easy to explain. I try it again:
In a single-field relationsship it is possible to have
records in the related table that are not related to the
primary table. This is the case for records that have Null
values in the related table field. Ok?
I want to do the same in a related table (T3) that has
1) a 2-field (F1, F2) relation to a primary table (T2) and
2) a single-field (F1) relation to another primary table
(T1).
Eample (there are other fields in T1,T2,T3 but of no
interest for this problem):
table field value
----- ----- -----
T1 F1 A

T2 F1 A
F2 B

This record is possible (it is not related to T1 nor to
T2):
T3 F1 Null
F2 Null

This record is not possible (it is related to T1 but shall
NOT be related to T2):
T3 F1 A
F2 Null
A want to have such records in T3. I see no reason why
this should not be possible. Referential integrity rules
could be applied to such constellations, but are obviously
not supported by Access (2000/2002).
Regards, Peter

 

[Tim Ferguson]

This record is not possible (it is related to T1 but shall
NOT be related to T2):
T3 F1 A
F2 Null
I want to have such records in T3. I see no reason why
this should not be possible.

This is probably because you do not really understand the meaning of
referential integrity. The creation of a Foreign Key constraint means that
any data in the field(s) must be valid in the target table: there would
have to be a record in T1 with key values (A, Null), which is plainly
garbage.

Of course you can have record in T3 that is not linked to any record in T1
-- for example, a patient without a doctor, or a PhoneNumber without an
owner. But in these cases, the entire Foreign Key must be Null, not just
part of it.

There cannot possibly be a record in T1 that has the value (F1="A" but I
don't know what F2 is), so it would be clearly impossible to validate such
a foreign key.

I noticed earlier in the thread that there is a high degree of redundancy
in your proposed schema, and it is likely that you need a radical re-think
of what your entities are and how they interact. What you want to do here
simply does not make sense.

Best wishes


Tim F

 

[Peter Zinniker]

-----Original Message-----


This is probably because you do not really understand the meaning of
referential integrity. The creation of a Foreign Key constraint means that
any data in the field(s) must be valid in the target table: there would
have to be a record in T1 with key values (A, Null), which is plainly
garbage.

Suppose you mean T2 instead of T1. The point is, that
there is no record (A, Null) in T2.

Of course you can have record in T3 that is not linked to any record in T1
-- for example, a patient without a doctor, or a PhoneNumber without an
owner. But in these cases, the entire Foreign Key must be Null, not just
part of it.

What about a patient with a doctor ("A" in T1) but without
a doctor's registration number (F2 in T2)?

There cannot possibly be a record in T1 that has the value (F1="A" but I
don't know what F2 is), so it would be clearly impossible to validate such
a foreign key.

I noticed earlier in the thread that there is a high degree of redundancy
in your proposed schema, and it is likely that you need a radical re-think
of what your entities are and how they interact. What you want to do here
simply does not make sense.

Best wishes


Tim F

Peter