4 Replies Latest reply on Feb 10, 2013 1:43 PM by Malcolm

    Enforcing Referential Integrity

    jmci

      FileMaker's Edit Relationship dialog offers the option "Delete related records in this table when a record is deleted in the other table". This enforces referential integrity by not permitting orphan child records to remain in the database when a record in the parent table is deleted. This is a great feature and it's great that it is built into the database. Sometimes it's necessary to perform a cascade delete to enforce referential integrity but sometimes the child records must not be deleted and it is necessary to restrict the deletion of the parent record. I wish FileMaker went one step further and added a feature where the user could prevent the deletion of a record on one side of the relationship if related records exist in the other.

       

      What is the best way to restrict the deletion of a parent record if child records already exist? Ideally the solution should be part of the database rather than a script called by a script trigger.

        • 1. Re: Enforcing Referential Integrity
          DavidJondreau

          I don't know of a way to do it without scripts. I tie it into a Custom Menu.

          • 2. Re: Enforcing Referential Integrity
            jmci

            Yeah, I've done the same thing in the past. Wouldn't somebody accessing my file from an external file still be able to delete a record in the parent table? Would there be a way to prevent that?

            • 3. Re: Enforcing Referential Integrity
              jmci

              Actually I've just remembered that this matter came up a couple of years ago and I have already been given a solution to this problem.

              It is done in the Manage Security dialog so it works even if the database is accessed from an external file. The catch is that the restriction cannot apply to any full-access account.

               

              In the Data Access and Design section of the Edit Privilege Set dialog, set Records to "Custom Privileges" and a Custom Record Privileges dialog opens. This dialog contains a list of each table in the database. Select the table to which you want to restrict delete and in the "Delete" drop-down select "Limited" and a Specify Calculation dialog opens. The calculation must have a Boolean result. Here is an example that I've tested and it works:

               

              If (

                IsEmpty ( ChildTable::_kf__ParentID__t ) ; // there are no child records

                1 ;

                0

              )

              • 4. Re: Enforcing Referential Integrity
                Malcolm

                Wouldn't somebody accessing my file from an external file still be able to delete a record in the parent table? Would there be a way to prevent that?

                 

                You are right. Custom menu's and scripted behaviours only work within the file. If the data is accessible from a different source then you need to address that.

                 

                User Accounts and Privilege Sets will give you the control you need. Privilege sets protect your data, regardless of how the file is being accessed.

                 

                In the "parent" table set a calculation in the Delete privilege which evaluates to false when there are related data sets present. Similarly, disallow deletion in the "child" tables.

                 

                Malcolm