Access not enforcing referential integrity!!!

[Danny]

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?

 

[Tom van Stiphout]

On Wed, 14 Apr 2010 20:26:01 -0700, Danny

I don't quite follow you: the "many-side table" and the "child table"
are the same thing.

-Tom.
Microsoft Access MVP

 

[Sylvain Lafontaine]

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]

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

 

[Roger Carlson]

You can give your foreign key a Default Value (ie. 0 (zero) for a numeric)
so that it will not be Null when a new record is created.

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

 

[John W. Vinson]

There is no way to allow or prohibit null values in an Access table.

Actually there is: set the field's Required property to Yes in table design
view.

 

[Danny]

!!! The default value of 0 is not working on subforms. I've set the default
value in both the table and the subform, but it doesn't seem to get set when
I attempt to enter data into the subform when the parent form record is
empty. Here is what happens:

- Main form record is empty
- Subform record is empty, and default value of 0 displays in the FK field
- I start typing data into the subform record, while main form record is
still empty
- FK field value changes from 0 to NULL

If I make the FK field Required, in addition to setting the default value to
0, then here's what happens:

- Main form record is empty
- Subform record is empty, and default value of 0 DOESN'T display in the FK
field
- I start typing data into the subform record, and immediately a prompt
tells me that I must enter a value in the FK field
- I click through the prompt, and finish entering data in the subform record
(main form record still empty)
- When I attempt to move off of the new subform record, I get a prompt
telling me that I can't change or add record because a related record is
required in the parent table

So this will do - it will prevent creation of orphaned records. But it seems
an awful lot of redundant field property values...

 

[John W. Vinson]

!!! The default value of 0 is not working on subforms. I've set the default
value in both the table and the subform, but it doesn't seem to get set when
I attempt to enter data into the subform when the parent form record is
empty. Here is what happens:

- Main form record is empty
- Subform record is empty, and default value of 0 displays in the FK field
- I start typing data into the subform record, while main form record is
still empty
- FK field value changes from 0 to NULL

Erm? What's the Master and Child Link Field? The Master should be the PK field
on the parent form, the Child should be the FK. I'd say that the FK field
should be required but that you should *not* use a zero default; that's more a
pain in the neck than any sort of benefit (since it will always be wrong and
need to be edited).

I suspect your master/child link isn't set up, or isn't working correctly.

 

[Fred]

Danny,

You mentioned entering on a subform in your last post.

Assuming that the subform is actually being used as a subform, the PK value
of the parent record is automaticaly loaded into the FK of the child record
when the user creates a new record in the the subform.

This is also the common way that this issue gets taken care of.

Fred

 

[BruceM via AccessMonster.com]

Assuming the Link Child and Link Master properties of the subform control are
properly set, as John pointed out.

 

[David W. Fenton]

I'd say that the FK field
should be required but that you should *not* use a zero default;
that's more a pain in the neck than any sort of benefit (since it
will always be wrong and need to be edited).

....and it won't work unless you create a record in the parent table
with 0 as the PK value.

 

[Roger Carlson]

That's pretty much the point. If there's no record in the parent table with
a 0 in the primary key, then no record with a 0 in the foreign key can be
saved.

 

[Roger Carlson]

I don't see the difference, really. A default value will be wrong and have
to be edited. A Null, required field will be "wrong" (that is, not allowed)
and have to be filled. Either way, the record can't be saved.

Still, setting the form/subform with the parent/child properties will
undoubtedly solve the problem.

--
--Roger Carlson
MS Access MVP
www.rogersaccesslibrary.com
http://rogersaccessblog.blogspot.com/

On Thu, 15 Apr 2010 10:38:06 -0700, Danny
<[email protected]>
wrote: I'd say that the FK field
should be required but that you should *not* use a zero default; that's
more a
pain in the neck than any sort of benefit (since it will always be wrong
and
need to be edited).