1 Reply Latest reply on Sep 23, 2014 7:28 AM by philmodjunk

    Referential Integrity to create one-to-many relationships.



      Referential Integrity to create one-to-many relationships.


      Hello, I have created the relational database (as shown on the screenshot), and I was wondering as to how I would create a one-to-many relationship, or to enforce referential integrity.

      Thank you for the help in advance!


        • 1. Re: Referential Integrity to create one-to-many relationships.

          You already have two one to many  relationships defined. Both Customer to Reservation and Seating to Reservation are one to many relationships.

          The typical way to set this up is to define a primary key field in the table on the "one" side of the relationship that auto-enters a serial number. With FileMaker 12 or newer, you can also set up a text field with Get ( UUID ) as an auto-enter calculation and you can specify a "unique values" validation field option on that field if you want your relationship to show as one to many like will happen with auto-entered serial numbers.

          You can further enforce referential integrity if you:

          a) select the "delete..." option for the table on the "many" side of the relationship. (Double Click the relationship line to get a dialog where you can select this option.)

          b) do not allow the primary key fields to be modified ever once the record has been created. Put a validation rule on the foreign key fields that requires that a matching record exist in the "one" table or only allow modification of the FK field via a value list that does not allow entering a non-matching value.

          Note: A properly designed data model that protects referential integrity creates challenges when you then try to design a FileMaker data entry layout that is sufficiently user friendly. You may find that the ideas presented in "Adventures in FileMaking #2 - Enhanced Value Selection" useful as the methods presented there can all be used in a way that works with properly implemented primary keys.

          Caulkins Consulting, Home of Adventures In FileMaking