Referential Integrity doesn't work - Acc07

[DarrelGiesbrecht]

Access 2007 - I started with a new database. I added 2 tables, each with a
key field. I added the key field of one table as a foreign key in the other.
I established a one to many relationship between them, setting referential
integrity. Why does my software allow me to add records to the many side of
the relationship, leaving the foreign key field blank? I've never had trouble
with this before. Is there some option in Acc07 that I haven't set?

 

[Dirk Goldgar]

Access 2007 - I started with a new database. I added 2 tables, each with a
key field. I added the key field of one table as a foreign key in the
other.
I established a one to many relationship between them, setting referential
integrity. Why does my software allow me to add records to the many side
of
the relationship, leaving the foreign key field blank? I've never had
trouble
with this before. Is there some option in Acc07 that I haven't set?

From the sound of it, you didn't set the foreign key field as Required. If
the foreign key is permitted to be Null, then you can freely create a record
in that table with no related record in the other table. Effectively,
you're saying, "this record may have no parent, but if it has one, it has to
be found in the parent table."

This is not new with Access 2007. To the best of my knowledge, it has
always worked this way.

 

[DarrelGiesbrecht]

I tried the exact same thing in Acc03. It works without setting the foreign
key to "Required". I think I'm missing something else.

 

[Dirk Goldgar]

I tried the exact same thing in Acc03. It works without setting the foreign
key to "Required". I think I'm missing something else.

Using Access 2003, I just created two tables,:

Table: T1
Field: T1ID (autonumber, PK)
Field: T1Desc (text)

Table: T2
Field: T2ID (autonumber, PK)
Field: T2Desc (text)
Field: T1ID (long) Required = No

Then I created a one-to-many relationship between T1 and T2, linking them on
the T1ID field in both tables. I enforced referential integrity on this
relationship.

Then I added some records to T1.

Then I added some records to T2 without filling in the T1ID field. No
error, no warning, no objection, no problem. Only when I tried to set
T2.T1ID to a value that was not in T1 did I get an error: "You cannot add or
change a record because a related record is required in table 'T1'."

This is exactly what I would have predicted. Are you sure you get a
different result in the same scenario? Is this not the scenario you are
talking about?

 

[DarrelGiesbrecht]

That's exactly what I did. However, I just discovered one difference. In
Acc03, the default for my tableT2:FieldT1ID was set to zero (rather than
empty). In fact, when I add any field to an Acc03 table with a field type of
"Number", I get a Long Integer with a default of zero. When I do the same
thing in Acc07, I get a Long Integer with the default blank. The referential
integrity will accept the blank without an error, but not the zero. Since it
was an automatic default, I never noticed it before. Any idea why I was
getting an automatic default of zero and you didn't?

 

[Dirk Goldgar]

That's exactly what I did. However, I just discovered one difference. In
Acc03, the default for my tableT2:FieldT1ID was set to zero (rather than
empty). In fact, when I add any field to an Acc03 table with a field type
of
"Number", I get a Long Integer with a default of zero. When I do the same
thing in Acc07, I get a Long Integer with the default blank. The
referential
integrity will accept the blank without an error, but not the zero. Since
it
was an automatic default, I never noticed it before. Any idea why I was
getting an automatic default of zero and you didn't?

You've put your finger on the discrepancy and discovered the reason for the
apparently different behavior in A2007. In Access 2003 and earlier
versions, number fields automatically get assigned a default value of zero.
This was caused so many problems for people and was the source of so many
complaints that, in Access 2007, that automatic default value was removed.
Naturally, in Access 2003 with the default value 0 in place for a foreign
key field, any new record you create will have a value in that field that
doesn't have a parent record (unless the parent table allows 0 as a primary
key), and so you'll get the referential integrity violation.

And the reason you and I got different results when testing with Access 2003
was that I automatically removed that default value when defining the field,
since I knew it would be a foreign key and it would make no sense to have a
default value for a foreign key. I didn't mention it when I described the
test procedure because it's become habit after so many years, and I did it
without thinking.

 

[DarrelGiesbrecht]

Thank you for your help. I had no idea of the change.