VistaDB 5
Foreign Key Integrity Operation Types

Possible Integrity Operations

Both the Update and Delete Integrity Actions may have the following settings.

No Action

When a row in the Primary Table is updated nothing happens in the Foreign Table.

If the EmployeeID is changed, none of the timesheets will be updated. This is typically used to just verify the data is correct at time of insertion, but that updates are allowed to change the database state afterwards.

Cascade

The same action should be performed on the Foreign Table entry as was performed on the Foreign Key entry. If the primary key row was deleted, then the foreign key entry is also deleted.

Most commonly this is used when you want the Foreign Table row to track the Primary Table row exactly.

Set Default

When the action is triggered the Foreign Table entry is reset to the Default value of the column. In order for this to work correctly you must first ensure that the Default entry for the column is functional and correct. Otherwise the action on the Primary Table will also fail as a result of the failed default.

The most common situation where I use this is when I have a DateTime that I want to be reset whenever a Primary Table is updated. Something like a LastModified field is kept on the Foreign Table and a default of 'GetDate()' is used.

When the action is triggered the local column then changes to the current date and time.

Set Null

Triggering this action will set the Foreign Key column to NULL. In order for this operation to complete the column must be nullable. The columns ability to be set to null is NOT checked at the time this option is set (by VistaDB or SQL Server), so ensure the column is set to nullable.

A common design scenario for setting a column to null is if you have a one to many parent child relationship, but the children do not have to be present.

When a parent record (like an account) has children that can be deleted (like help requests) this is a good usage. You may have a column for MostRecentRequest that includes the ID of the most recent help request. When the request is removed from the system the MostRecentRequest column is set to NULL meaning there are none present.

See Also