 |
|
SQL Server Tips by Burleson |
Referential Integrity
Referential Integrity is a feature provided by relational database
management systems that prevents inconsistent data. Because of the
relationships between tables, changes in primary keys must be
updated in foreign keys. This can lead to cascaded updates or
deletes, if more than one row contains the affected foreign key. The
table with the foreign key must not allow the insertion of rows not
containing a value from the related table.
SQL Server enforces Referential Integrity with two different
techniques named Declarative Referential Integrity (DRI) and
triggers.
DRI is declared when creating or altering a table with a table
property. This is done with the clause FOREIGN KEY...REFERENCES,
this is a constraint that ensures that the column or columns respect
the referential integrity. A FOREIGN KEY constraint can only
reference a PRIMARY KEY or UNIQUE key in the referenced table. There
are two possible actions that activate the constraint: delete row
(ON DELETE) or update row (ON UPDATE). The constraint can take one
of two actions: CASCADE means that the DELETE or UPDATE will also
occur on all the related rows, and NO ACTION which raises an error
and rolls back the action.
Triggers provide cross-database referential integrity while DRI only
works within one database. The code in a trigger can do as much as a
SP, with minor exceptions, particularly with database statements.
Other than that, the code is very flexible and with access to
conceptual tables providing direct access to the modified rows. That
is where the strength of the triggers lies; they can not only send
emails, track changes, even execute applications when called but
also examine the changes at row level and make decisions or take
actions based on it.
DRI is faster and easier to maintain because it relies on a
constraint with no code underneath while triggers demand code to
handle properly all the relationships. Using triggers for
Referential Integrity can be difficult when there are many tables
related to each other and changes in the data structure will cause
all the code to be changed and carefully reviewed. The advantage of
using triggers is that the code can be customized to do tasks that
are more intricate and it will allow circular references in the data
structure, which DRI strictly forbids.
The above book excerpt is from:
Super SQL
Server Systems
Turbocharge Database Performance with C++ External Procedures
ISBN:
0-9761573-2-2
Joseph Gama, P. J. Naughter
http://www.rampant-books.com/book_2005_2_sql_server_external_procedures.htm |
