Physical Modeling › Triggers › Referential Integrity to Enforce Business Rules

Referential Integrity to Enforce Business Rules

Business rules are logical constructs that express how a business uses its data. You can use a set of built-in referential integrity (RI) options to capture common business rules that apply to relationships between data that you maintain in different tables. You can enforce the rules using a variety of different programming techniques, including the following:

• Application logic, such as front-end applications or stored procedures.
• Declarative referential integrity, declaring which RI rule to enforce for each table relationship in the schema.
• Triggers, which are segments of SQL code that are automatically executed by the database when a SQL INSERT, UPDATE, or DELETE command is executed against a table.

One of the best ways to enforce RI rules is through the use of triggers. Unlike the coding in front-end applications, triggers are centrally located, so that if the business rule changes, the change can be implemented once. Triggers also support the enforcement of additional rules, beyond those supported by declarative referential integrity. For example, in an Order Entry database, if you want to flag backorder items when creating a row in the CUST_ORDER table, you can create a custom trigger to check a quantity_on_hand value for a product when inserting a row into the ORDER_LINE table.

CA ERwin Data Modeler triggers can handle the most common rules for enforcing referential integrity without any additional programming or customization. For example, the question, "Can I delete an order that has one or more order line details?" can be answered by a business rule and can be enforced by a built-in trigger. If the business answer to the question is:

• "No, if there is a line item the order cannot be deleted," you can enforce the behavior by attaching the Parent Delete-RESTRICT trigger to the relationship between the CUST_ORDER and ORDER_LINE tables. Once the trigger is generated, when a user attempts to delete a row in the CUST_ORDER table, the delete fails if there are one or more rows in the child ORDER_LINE table.
• "Yes, if there is a line item the order can be deleted and the line item must also be deleted," you can enforce the behavior by attaching the Parent Delete-CASCADE trigger to the relationship between the CUST_ORDER and ORDER_LINE tables. When a user deletes a row in the CUST_ORDER table, the dependent rows in the child ORDER_LINE table are also deleted.

In a similar way, the business rule might specify that the dependent rows in the child ORDER_LINE table should not be deleted when the row in the CUST_ORDER table is deleted, but that the foreign key value should be set to NULL or to a default value. You can handle each of these situations using CA ERwin Data Modeler-generated triggers.

More information:

Triggers

Set Default Referential Integrity Rules