Referential Integrity

[jmci]

I often want to ensure that, if a user deletes a record in a parent table, all records in a related child table also get deleted (cascade delete). I can ensure this in the Edit Relationship dialog by checking the option "Delete related records in this table when a record is deleted in the other table".

What if I want to prevent the user from being able to delete a record in a parent table if that table has related records in a child table? What are my options? Is there a way to ensure this in the database schema or do I just have to rely on the scripted control of my user interface?

[Vaughan]

Set this up in the privilege set's record level access.

[jmci]

But that would stop them from deleting any record in a specified table under any circumstances and that's not what I want. I only want to stop them from deleting a parent record if it already has children.

[bcooney]

No, that is not what Vaughan said. Specify in the calc for the RLA

Allow delete if

isempty ( relationship::_kF_ParentID) // there are no children

I usually script this. So, in my Delete script:

If ( isempty (relationship::_kF_ParentID) //there are no children

Delete Record

else

Show dialog "What about the kids?"

endif

[jmci]

Forgive my inexperience but what do you mean by the "calc for the RLA"?

[bcooney]

In the Security section, you define record level access rules. If you have limited edit capabilities, then to define when a user can edit, you can enter a calc. See Help.

[jmci]

OK, I see it. That would work. Thanks

Now, would that enforce my referential integrity delete rule in all circumstances such as if the file was accessed by another external file?

[Vaughan]

The record level access restrictions are ALWAYS enforced as long as the file is opened with an account that uses them. That is, don't use the full access account.

This is difference between enforcing restrictions at the record level (using the security privileges) and using interface (scripting the delete command). The RLA security works even when the interface is in a different file.

[jmci]

That's what I wanted. I knew that enforcing RI at the interface level wouldn't be good enough in every situation.