Referential Integrity

[Walter]

I need someone to verify my understanding of referential
integrity. I have a form based on a table with two FK's
relating it to two other tables. There will be an entry
in either one or the other of these fields.
The "required" property is set to No in both the table
and the form. If Referential Integrity is set on these
relationships, it requires an entry before the record can
be saved, correct? In other words, in this type of
situation, you can't enforce referential integrity. I
read all I could find but didn't anything specifically
addressing this.
Thanks,
Walter

 

[Allen Browne]

No. Referential Integrity does NOT require a value in the foreign key field.

You are permitted to have a Null in the foreign key field, even with RI
enforced.

 

[Walter]

Why then would I get an error message when entering a
record with no entry in one of these fields or if a
record has been entered without referential integrity and
I try enforce it, I get an error message saying Data in
Table violates integrity rules? I looked back at my
table setup and found this field was Indexed, Duplicates
OK. Is this the culprit and should not be indexed?
Thanks,
Walter

 

[Allen Browne]

If the foreign key is a Number type field, Access will set the Default Value
to 0. This value does violate the integrity (assuming you don't have a zero
in the primary table's key field).

There could be other reasons, such as a Validation Rule on the field.

 

[Walter]

This is a number field since the Primary Key it the
related table is an autonumber. Is there a work around
to maintain integrity in this type of situation?
Thanks,
Walter

 

[Allen Browne]

If you do not want to permit nulls in the foriegn key, then open the related
table in design view, and set the Required property of the foreign key field
to Yes.

For more detail, see Error #3 in article:
Common errors with Null
at:
http://members.iinet.net.au/~allenbrowne/casu-12.html

 

[Douglas J. Steele]

If the field's default has been set to 0, change that.

 

[Walter]

Setting the required property to yes means there has to
be an entry in order to save the record, correct? In the
situation where there should not be an entry, this would
not work. Situation: A church has a list of officers;
President, Vice-President, Treasurer, Secretary. The
President and Vice-President will always be members and
have a MemberID, however, the Treasurer and/or Secretary
could be non-member staff thereby having a PersonelID
instead of MemberID. Both would be number FK fields in
the Officers table but only one could have an entry for
each record. In this situation, referential integrity
could not be enforced since the 0 set by Access will not
match any records in the primary table, correct?
Thanks for the information,
Walter

 

[Allen Browne]

Walter, what I don't understand is why you want the zero.

The correct value to use is Null, i.e. if the person has no memberid, leave
the field blank. You can then use a relationship with RI enforced.

To achieve that:
1. Open the related table in design view, and set these properties for the
MemberID:
Required No
Defaul Value {remove the zero. Nothing in this property}

2. Save and close the table.

To fix the problem for the existing records that have a zero:
1. Create a query into the related table.
2. Drag the MemberID field into the grid.
3. In the Criteria row under this field, enter: 0
4. Change it to an Update query (Update on Query menu).
5. Leave the Update row blank.
6. Run the query. This converts all the zero values into Nulls.
7. Close the query (no need to save).

You should now be able to create a relation with RI enforced. If not, use
the Unmatched Query Wizard to find the other bad values also.

 

[Walter]

Thanks Allen for your help. I misunderstood your
previous post by thinking the "0" was assigned at the
time the record was created. I have now deleted the 0
from the DefaultValue in the table. I appreciate your
help.
Walter