Does RI with nullable FKs really work?????

[tmj00345]

I've taken RI off all my relationships in which the foreign key can be null,
because Access doesn't seem to allow a FK to be null if RI is defined on it.

I'd be amazed if Access did not permit RI on nullable FKs, so first thing is
to ask the experts here: does it?

If the answer is "yes", then I'll post the details of my own situation, in
which I can't leave a FK null if RI is defined on it.

thanks.

 

[Allen Browne]

Yes: Access does permit nulls in the foreign key value, even if referential
integrity is enforced.

The most common reason people have problems with gettting this to work is
with Number fields. Access sets zero as the Default Value for your number
fields, so if you leave it blank, Access tries to assign a zero to the
foreign key, and since that probably doesn't match any record in the primary
key, the assignment fails.

 

[tmj00345]

Thanks Allen. I'll look into it. If it turns out that there is an "un-common"
reason for my problem, I'll post again.