I did not know this - that null foreign key values are allowed. Wow. I've
worked with relational databases for over ten years, too. Hmm...

BUT: There is no way to allow or prohibit null values in an Access table.
For a text field, zero length strings can be allowed or prohibited, but not
null values. Another way to deal with this is to set Required to Yes for
these fields. But I have to do something, because I don't want users being
able to inadvertently enter data into a subform where the main form has no
data.

Thanks for the education in RI and null values

"Sylvain Lafontaine" wrote:

Referential integrity is checked only for Non-Null values; so this is the
expected behavior here. If you don't want to have any Null value in your
foreign key, set it up as Not-Null in order to forbid null value in the
first place.

--
Sylvain Lafontaine, ing.
MVP - Windows Live Platform
Blog/web site: http://coding-paparazzi.sylvainlafontaine.com
Independent consultant and remote programming for Access and SQL-Server
(French)


"Danny" wrote in message
...
Howdy.

Using Acc 2007. Have two tables related one-to-many. Ref. integrity being
enforced, without any cascading updates or deletes. Data in both tables.

Problem: I can add new records to the many-side table, without any value
for
the foreign key, and the new record is created without error or prompt
from
Access. I can do this both in the child table directly, and using a
form/subform.

On the other hand, if I create a new child record and enter a FK value
that
doesn't exist in the parent table, I get a message saying that a related
record in the parent table is required, and Access prohibits creation of
orphan.

What the heck is going on?


.