DML, Integrity, and Constraints


What sets the SELECT statement, covered in the preceding chapter, apart from the INSERT, UPDATE, and DELETE statements is that the latter three result in changes being made to tables. Rows are inserted or appended with INSERT, values are changed with UPDATE, and rows are removed with DELETE. Each action will cause any triggers or check and integrity constraints defined on the target tables and columns to trap your code, and it is thus important to take this into account. For example, if an INSTEAD OF trigger is defined on a DELETE statement when your DELETE statement hits the table, the code in the trigger will run instead. The code might still delete the row, but based on a condition that can be checked at trigger run time, not on something the client process would know about.

The integrity constraints and how they work is adequately covered in Chapter 12, and triggers in Chapter 13; however, a number of aspects and new features must be discussed in this chapter, especially the new cascade operations.

Cascading Referential Integrity

Cascading referential integrity (CRI) applies to the DELETE and UPDATE statements only because they cause changes to existing rows. The CRI constraints let you define the course of action SQL Server should take when your statement hits tables on which foreign keys depend.

For example, let’s say you want to delete a customer from the Customers table. What then should happen to that customer’s records that still exist in the Orders table? The rows in the Orders table would then be separated from any rows in the Customers table, and thus, depending on your solution, they may no longer be accessible.

When you create a table using T-SQL DDL as discussed in Chapter 10, you can specify the action to take upon a DELETE or an UPDATE statement hitting the table. There are two courses of action that can take place for either operation:

  • CASCADE or take no action on Delete ([ ON DELETE {CASCADE | NO ACTION} ])

  • CASCADE or take no action on Update ([ ON UPDATE {CASCADE | NO ACTION} ])

The NO ACTION condition is the default if you incur a referential integrity condition but do not specify anything. If you do specify the cascade operation, SQL Server will cascade the DELETE or UPDATE operations to the related rows in the foreign tables. For example, a cascading delete will result in all the orders relating to the deleted customer being removed as well from the Orders table. This new feature is an incredible time saver as far as referential integrity is concerned, and I use it all the time now. It certainly saves you a ton of time having to code manual cascade operations that have you doing queries against foreign tables and then deleting the related rows.

There can also be multiple cascading actions that can take place if triggers and collateral constraints fire as a result of the cascade. For example, deleting a row from T1 can cascade to delete a row from T2. A cascading delete defined on T2 can thus cause a cascading delete on table T3, much like the domino effect.

A cascading tree or nest structure will begin to emerge, and this is okay as long as the cascade does not become circular and reference a row in a table that has already been axed or updated.




Microsoft SQL Server 2005. The Complete Reference
Microsoft SQL Server 2005: The Complete Reference: Full Coverage of all New and Improved Features
ISBN: 0072261528
EAN: 2147483647
Year: 2006
Pages: 239

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net