313
Chapter 12: Modifying Data In SQL Server
12
REFERENCES [HumanResources].[Employee] ([BusinessEntityID])
ON DELETE CASCADE
GOAs a caution, cascading deletes, or even referential integrity, are not suitable for every
relationship. It depends on the permanence of the secondary row. If deleting the primary
row makes the secondary row moot or meaningless, then cascading the delete makes good
sense; but if the secondary row is still a valid row after the primary row is deleted, then
referential integrity and cascading deletes would cause the database to break its represen-
tation of reality.Alternatives to Physically Deleting Data
Some database developers choose to completely avoid deleting data. Instead, they build
systems to remove the data from the user’s view while retaining the data for safekeeping
like dBase did. This can be done in several different ways:■ (^) A logical-delete bit fl ag, or nullable MomentDeleted column is added to each row.
The bit fl ag is set to zero by default. When the row is deleted it is set to 1, mark-
ing the row as logically deleted. The MomentDeleted is a datetime column that is
null initially. When the row is deleted the column is updated to the date and time of
the delete. This makes deleting or restoring a single row a straightforward matter
of setting or clearing a bit. However, because a relational database involves multiple
related tables, there’s more work to it than that. All queries must check the logical-
delete fl ag and fi lter out logically deleted rows. This means that a bit column (with
extremely poor selectivity) is probably an important index for every query. Although
SQL Server’s fi ltered indexes are a perfect fi t, it’s still a performance killer.
■ (^) The cascading logical deletes method is complex to code and diffi cult to maintain.
This is a case of complexity breeding complexity, so the authors no longer recom-
mend this method.
■ (^) Another alternative to physically deleting rows is to archive the deleted rows in an
archive or audit table. This method is best implemented by an INSTEAD OF trig-
ger that copies the data to the alternative location and then physically deletes the
rows from the production database. This method offers several advantages. Data
is physically removed from the database, so there’s no need to artifi cially modify
SELECT queries or index on a bit column. Physically removing the data enables
SQL Server referential integrity to remain in effect. In addition, the database is
not burdened with unnecessary data. Retrieving archived data remains relatively
straightforward and can be easily accomplished with a view that selects data from
the archive location.
Chapter 42, “SQL Audit,” details how to automatically generate the audit system discussed here that
stores, views, and recovers deleted rows.
c12.indd 313c12.indd 313 7/30/2012 4:42:41 PM7/30/2012 4:42:41 PM
http://www.it-ebooks.info