Declarative Referential Integrity vs. Triggers

How can you decide whether to use Declarative Referential Integrity (DRI) or triggers to enforce table relationships when you upsize from Access 97 to SQL Server? DRI lets you use a CREATE TABLE or ALTER TABLE statement to declare the foreign key reference from one table to another, thus making referential integrity a database table property. In the SQL Server environment, you use triggers to enforce referential integrity, but triggers are code, which doesn't execute as quickly as a table property. The Upsizing Wizard’s Export Table Attributes screen lets you choose either DRI or triggers to enforce referential integrity. The scheme you can use depends on how you created the table relationships in Access 97.

To identify the original relationships in Access, open the Relationships window (Tools, Relationships), click each line that connects two tables, then right-click to open the shortcut menu. Choose Edit Relationship to open the Relationship dialog box. The grid at the top of this box shows the two tables in the relationship and the fields in each table that anchors the relationship. Beneath the grid are three check boxes; the top check box label is Enforce Referential Integrity, followed by Cascade Update Related Fields and Cascade Delete Related Fields. If, for the relationships in your Access database, you chose only Enforce Referential Integrity, you can use the DRI option on the wizard. If you chose Cascade Update Related Fields or Cascade Delete Related Fields for any relationships, you must choose the wizard’s Trigger option. Cascading DRI refers to a relationship in which at least one row in a dependent table is automatically modified when the corresponding row in a parent table is modified. For example, you might use cascading DRI when a row in an order header (master) table is deleted and the delete operation is automatically applied—cascaded—to the corresponding rows in the order detail table.

DRI causes SQL Server to create its own tables with the Access relationships and references. Unfortunately, SQL Server's DRI doesn't support cascade update or cascade delete, so if you choose DRI, you lose any update or delete cascading functionality you have in Access. DRI executes faster and more efficiently than triggers, but you need to enforce cascading actions in SQL Server with triggers.