Referential integrity not being enforced

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Despite enforcing referential integrity between my tables, I am able to
create a record in the child table without a corresponding record in the
parent table.

For example, tblPatients has a one-to-many relationship to tblVisits. (A
patient can have many visits.) They are linked by PatientID.

tblPatients
------------
PatientID (PK)
FName
LName
…

tblVisits
------------
VisitID (PK)
PatientID
VisitDate
VisitLocation
…

The main form, frmPatients, has one subform, fsubVisits. They are linked by
PatientID.

I can enter any number of records into fsubVisits without ever entering a
patient, and I don’t get the usual error message when I try to leave the
record or subform.

Any idea what’s overriding the referential integrity?

Thanks.

Kurt
 
It is possible that you have a PatientID with a value of zero and have the
default value for PatientID in tblVisits set to 0. Access doesn't know when
a long integer is going to be used as a FK so it automatically assigns 0 as
the default value for all numeric fields. You need to remove the 0 so that
the default will be null. Then you need to set the required property of the
FK to Yes.
 
Back
Top