Cascade Updates and Deletes in an EQuIS Database

When attempting to update or delete a value in EQuIS that has a foreign-key constraint to another value in EQuIS, there are two potential responses from the SQL Server Database.

1.Prevent the update/delete: Warns the user that doing so would break referential integrity (default behavior in many/most relational database models).

2.Allow the update/delete: And also update/delete the value where it exists in one or more related records in the database. This is known as a cascade action, either cascade update or cascade delete.

By default, the EQuIS Professional Schema does not include explicit cascade actions for any of the foreign-key constraints within the database. Cascade actions can however be enabled using SQL Server, and EQuIS Professional will treat all relationships as specified in SQL Server.

Note: Cascade updates and cascade deletes are not a good solution in all situations. Each EQuIS Database Administrator (DBA) must decide if and when, and to which particular relationships these actions should be applied (see Cascade Action examples below). Consult the DBA before making any changes to the default properties.

Cascade Action Examples

Possible cascade actions might be any of the following.

•Apply cascade actions to only one specific relationship.

•Enable cascade updates, but not deletes on all relationships.

•Temporarily allow cascade actions, and then later disallow them.

Any cascade actions explicitly enabled will not be modified during an EQuIS Schema update. However, any new relationships that are created during the EQuIS Schema update (if applicable) will be created with the default of no cascade actions.

Cascade Delete Button in EQuIS Professional

In EQuIS Professional, the Cascade Delete button can be used to delete a record and any related child records.

Note: Since references to a facility are much more complex than for most tables, this cascade delete functionality is not optimal for deleting a facility from an EQuIS Database. First investigate other means, such as the equis.delete_facility stored procedure.

Visibility and use of this button are controlled by Application Level Security (ALS) permissions, and whether the table is a Restricted Table. Additionally, it is controlled by using the CascadeDelete setting in the ST_CONFIG table.

Note: The ST_CONFIG settings will not override ALS Permissions that would otherwise prevent having access to the Cascade Delete functionality (having less than Editor permission on the logged in facility) nor if the table is a restricted table.

Cascade Actions in SQL Server

Refer to Microsoft's Cascading Referential Integrity (Primary and Foreign Key Constraints) page.