INF: Cascading Deletes and Updates of Primary Keys (142480)


The information in this article applies to:

  • Microsoft SQL Server 6.0
  • Microsoft SQL Server 6.5
  • Microsoft SQL Server 7.0
This article was previously published under Q142480

SUMMARY

Microsoft SQL Server provides declarative referential integrity (DRI) which allows you to define data integrity restrictions for a table as well as relationships between tables, both of which are enforced by SQL Server automatically at the system level.

SQL Server conforms to ANSI Entry SQL with regard to referential integrity between PrimaryKey and ForeignKey columns which requires the inserting, updating, and deleting of data in related tables to be restricted to values that preserve the integrity.

ANSI Intermediate SQL adds 'referential actions,' which describe what should be done to dependent ForeignKey values when their corresponding PrimaryKey values are updated or deleted. This article describes how such cascading deletes and updates can be implemented with SQL Server.

MORE INFORMATION

DRI preserves the defined relationships between tables when records are inserted, updated, or deleted. DRI is based on FOREIGN KEY, PRIMARY KEY, and UNIQUE constraint relationships, and it ensures that related key values are consistent. SQL Server uses PRIMARY KEY, FOREIGN KEY/REFERENCES, and UNIQUE constraints to enforce DRI; a ForeignKey can refer to columns declared as either a PrimaryKey or with a Unique constraint.

The Restrict-Only level of referential integrity requires that the following four actions be detected and prevented from completing successfully:
  1. Inserting a row in a referencing table where the value of a ForeignKey does not match a PrimaryKey value in the referenced table.
  2. Updating a ForeignKey value in a row in a referencing table so there is no matching PrimaryKey value in the referenced table.
  3. Updating a PrimaryKey value in a row in a referenced table so a ForeignKey in a row in the referencing table no longer has a matching PrimaryKey value.
  4. Deleting a row in a referenced table so a ForeignKey in a row in the referencing table no longer has a matching PrimaryKey.
Cascading updates and deletes provide an alternative to merely restricting the occurrence of the last two actions above. When a PrimaryKey is updated, as in number three above, a Cascading Update would cause all referencing ForeignKeys to be updated to the new PrimaryKey value. When a PrimaryKey is deleted, as in number four, a Cascading Delete would perform one of three actions:
  1. Delete the rows that referenced the deleted PrimaryKey (CASCADE).
  2. Set the referencing ForeignKey values to NULL (SET NULL).
  3. Set the referencing ForeignKey values to the column's default value (SET DEFAULT).
Cascading updates (of primary keys) and deletes can be implemented by using either triggers or stored procedures. Descriptions of each are given below. The use of stored procedures is preferred because the cascading functionality can coexist with declared ForeignKeys. To use triggers to supply the same functionality, ForeignKeys cannot be declared.

Note that, given the definition of a primary key, changing a primary key value should be a relatively rare occurrence; deleting a primary key should be a less rare operation.

Cascading Deletes/Updates with Stored Procedures

Cascading deletes and updates can coexist with DRI ForeignKey and References constraints as long as the cascading operations are performed before the corresponding constraint checks are done. The data manipulation statements (inserts, updates, and deletes) should be managed via stored procedures rather than allowing users to directly manipulate table data; the statements could be done directly, but encapsulating them in stored procedures provides additional security and maintainability. The users call the appropriate stored procedure for the required operation; permission is granted on the stored procedures and revoked from the tables to ensure proper use.

The cascading update stored procedure would first insert a new row in the primary table, duplicating all values of the existing row, but including the new primary key value. It would then update the foreign keys in the dependent table(s) and then delete the original row in the primary table.

The cascading delete stored procedure would first either delete the foreign key rows or update them to either NULL or their default value. It would then delete the row in the primary table.

If further cascading was needed, the delete or update of the foreign keys would be done using another delete/update stored procedure for the dependent table.

The insert statement does not require any special processing beyond what DRI automatically provides, so the insert could be done directly or wrapped in a stored procedure for consistency.

To allow for multirow updates and deletes, it may be necessary to create temporary tables containing the primary keys of the rows that are to be processed, and then create a cursor on the temporary table to call the procedure for each row, one by one. This is because a range of rows cannot be passed to a subsequent update or delete stored procedure to perform the cascading operation.

Cascaded Deletes/Updates with Triggers

Triggers cannot be used to perform cascading updates and deletes if ForeignKey-to-PrimaryKey relationships (or ForeignKey-to-Unique relationships) have been established using SQL Server's DRI. The DRI constraints are tested first; the trigger only fires if the update or delete passes all constraint restrictions. Therefore, because any update or delete that would need to be cascaded would fail the constraint checking, DRI ForeignKey constraints must not exist on those relationships that need to be cascaded.

By not declaring the ForeignKey (or References) constraints, the cascading updates and deletes can be implemented using triggers. The PrimaryKey and Unique constraints should still be used, however.

A delete trigger on the primary table either deletes the rows in the dependent table(s) or sets all corresponding ForeignKeys to Null (or their default value). The cascading delete is easily performed with nested triggers, each deleting all rows in dependent tables. The cascading SetNull and SetDefault may be more problematic due to multirow considerations with triggers if these updates must be cascaded to additional levels of dependent tables. However, if the ForeignKeys are not also part of the dependent table's PrimaryKey, they can simply be updated from within the trigger.

For implementing cascaded updates, an update trigger on the primary table should perform the required data modifications on the secondary table(s). Again, as long as the ForeignKey being updated is not part of the dependent table's PrimaryKey, it can simply be updated from within the trigger.

The documentation provided with SQL Server 4.2x provided examples of update and delete triggers that enforced referential integrity. The trigger documentation provided with SQL Server describes triggers used for business rule enforcement rather than referential integrity, but the information on 'How Triggers Work' and 'Multirow Considerations' is informative (see the SQL Server "Database Developer's Companion," Chapter 6).

The following is an example of a cascading delete trigger on the titles table that deletes all rows in titleauthor table with matching ForeignKey values. Because title_id is part of the PrimaryKey of titleauthor, this trigger assumes there are no subsequent levels of tables with ForeignKeys referring to titleauthor. Note that this will work correctly even for multirow deletes. 
CREATE TRIGGER DelCascadeTrig
  ON titles
  FOR DELETE
AS
  DELETE titleauthor
    FROM titleauthor, deleted
    WHERE titleauthor.title_id = deleted.title_id

The following is an example of a SetNull delete trigger on the titleauthor table that updates all rows in the titleauthor table with matching foreign key values. Again, because title_id is part of the PrimaryKey of titleauthor, this trigger assumes there are no subsequent levels of tables with ForeignKeys referring to titleauthor. This will work correctly even for multirow deletes. 
CREATE TRIGGER DelSetNullTrig
  ON titles
  FOR DELETE
AS
    UPDATE titleauthor
      SET titleauthor.title_id = NULL
        FROM titleauthor, deleted
        WHERE titleauthor.title_id = deleted.title_id

The following is an example of a cascading update trigger on the titles table that updates all rows in the titleauthor table with matching foreign key values. Again, because title_id is part of the PrimaryKey of titleauthor, this trigger assumes there are no subsequent levels of tables with ForeignKeys referring to titleauthor. 
CREATE TRIGGER UpdCascadeTrigBad
  ON titles
  FOR UPDATE
AS
  IF UPDATE(title_id)
  BEGIN
    UPDATE titleauthor
      SET titleauthor.title_id = inserted.title_id
        FROM titleauthor, deleted, inserted
        WHERE titleauthor.title_id = deleted.title_id
    END
  END

This will NOT work correctly for multirow updates, because there is no way to match a given row in the deleted table with its corresponding row in the inserted table without adding a second unique identifier that never changes its value. This is the same problem that arises when the cascading needs to be taken to subsequent levels when the ForeignKey is part of the PrimaryKey in the dependent table, and the PrimaryKey in the dependent table is referred to by other ForeignKeys.

To prevent multirow updates, the preceding trigger should be rewritten to prevent the update from affecting more than one row of the original table (titles, in this case). Note that the update in the trigger may well update more than one row in titleauthor; this solution to the multirow problem might just cause the problem to reappear at the next level of the cascade. 
CREATE TRIGGER UpdCascadeTrig
  ON titles
  FOR UPDATE
AS
  IF UPDATE(title_id)
  BEGIN
    IF @@ROWCOUNT = 1
      UPDATE titleauthor
        SET titleauthor.title_id = inserted.title_id
          FROM titleauthor, deleted, inserted
          WHERE titleauthor.title_id = deleted.title_id
    ELSE
      ROLLBACK TRANSACTION
      RAISERROR ('Multi-row update on table "titles" not allowed.')
    END
  END

Note that for multiple levels of cascading triggers to work at all, the 'nested triggers' sp_config parameter must be '1' and that triggers can only be nested to 16 levels.

Modification Type:MajorLast Reviewed:9/16/2003
Keywords:kbinfo kbusage KB142480
©2003 Microsoft Corporation. All rights reserved.