Enforcing referential integrity

[enxio27]

I just read in another thread about enforcing referential integrity between tables so that deleting a record in a primary table won't leave orphans in a subordinate table. I was able to edit the relationship for one subordinate table to enforce referential integrity (cascade update and cascade delete), but not for the other two. It keeps giving me an error saying that referential integrity for those two tables can't be enforced. I've gone through every record in those two tables, and I can't find any more orphans. What am I doing wrong?

 

[gemma-the-husky]

the fields that you are trying to match have to be of the same type.

 

[enxio27]

They're both long ints. I have an autonumbered ID field in the Members table that is used as the related field in the subordinate tables. Why it's working with one subordinate table but not the other two is beyond me.

 

[gemma-the-husky]

i'm not sure - perhaps it has to be defined as a key in both tables before you can enforce the referential integrity. Alternatively, if it can't be enforcrd you might have some rogue values in the subrodinate table. Dry doing an unmatched query to find any floating values in the subordinate table

 

[enxio27]

You were right about the floating values. I thought I had gotten them all on a visual check. I thought there was a way to create an unmatched query, but I had forgotten how to do it. That caught the remaining orphans. Thanks!