Cascade Updates and Deletes

<< Click to Display Table of Contents >>

EQuIS 7  >>  Professional > Databases > Admin >

Cascade Updates and Deletes

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 Data delete-WF 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, the cascade delete functionality here is not the best way to delete a facility from an EQuIS Database. Other means such as the equis.delete_facility stored procedure should be used instead.

 

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 Constraints (Foreign Key Constraints) page, http://technet.microsoft.com/en-us/library/ms186973%28v=sql.105%29.aspx.