referential integrity not working?

[tina]

hi folks.
i don't know if i have a problem with my software, or if i'm having a stupid
attack at this hour on a Sunday evening.
i built a number of tables with child tables, and some of the child tables
in turn have child tables. set up the primary and foreign key fields as
usual, linked them all in the Relationships window, and enforced Referential
Integrity on each link - all of this just as i always do.
*Access is not enforcing referential integrity, despite the checkmarked box
in the dialog box in Relationships window.* i can enter a record in a child
table, with the foreign key field left blank - and in fact no records
existing in the parent table at all - and the record saves without error. i
can do this at the form level and at the table level.
I am using Win2K Pro OS, Access 2003 software as part of Office 2003 Pro,
and i have tested this in an A2000 mdb and an A2002/3 mdb.
is something wrong here - and if so, any ideas what? - or am i going crazy?

tia
tina

 

[Tim Ferguson]

i can enter a record in a child
table, with the foreign key field left blank - and in fact no records
existing in the parent table at all - and the record saves without error.

That is alight: a NULL in a foreign key field is perfectly legal. If you
want to make a mandatory relationship, then make the FK field a Required
one.

Example: you should not be able to enroll a student on a course unless that
student has already been registered at the college, so Enrolled.StudentID
would be a Required field.

Example 2: Some employees are heads of department and so do not have a
Manager; at other times a HoD resigns before a replacement is made so all
the emps in that department do not have a HoD. In that case,
Employees.HeadOfDept field would _not_ be a required field as it has to be
able to hold a null value.

In your circumstances, you have to understand exactly what relationships
you are setting up and what is legal and what is not.

Hope that helps


Tim F

 

[tina]

yes, i realized that about 2 am this morning after sleeping for about 3
hours. i was, after all, just having a stupid attack on a sunday evening.
but thanks for responding, Tim.