Maintaining Referential Integrity

[James r]

I have an application with a split DB; a backend residing
on a server and a frontend residing on user desktops. The
DB design includes a primary table with several "child"
tables related to the primary by a "one-to-many"
relationship. At least one of the "child" tables has its
own "one-to-many" related tables. The relationships are
all "enforced" with cascading updates and cascading
deletes.

Overtime, as users add new, update, and delete records,
the relationships mysteriously disappear. The result is
multiple orphan records in the child tables. What I can't
figure out is how the orphan records came to be if
referential integrity is enforced. Shouldn't that prevent
orphan records?

Anyone have any suggestions?

 

[John Vinson]

Overtime, as users add new, update, and delete records,
the relationships mysteriously disappear. The result is
multiple orphan records in the child tables. What I can't
figure out is how the orphan records came to be if
referential integrity is enforced. Shouldn't that prevent
orphan records?

It certainly should!!!

Are the relationships defined *in the backend*? Only there can they be
enforced.

The most likely situation is PEBKAC - "Problem Exists Between Keyboard
And Chair". Some user who knows too much (but not as much as they
think they know) may be getting around those pesky error messages
about "unable to insert record because of key violation" by opening
the backend, deleting the relationships, and neglecting to recreate
them.