Call now: (800) 766-1884  



 Home


 SQL Server Tips
 SQL Server Training

 SQL Server Consulting
 SQL Server Support
 SQL Server Remote DBA



 Articles
 Services
 SQL Server Scripts
 Scripts Menu



 

 

 

   
  SQL Server Tips by Gama and Naughter

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  

 

Burleson Consulting Remote DB Administration


 

 


 

 

 

 

 
Burleson is the America's Team

Note: The pages on this site were created as a support and training reference for use by our staff of DBA consultants.  If you find it confusing, please exit this page.

Errata?  SQL Server technology is changing and we strive to update our SQL Server support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:and include the URL for the page.
 


Burleson Consulting
SQL Server database support

 

Copyright 1996 -  2013 by Vaaltech Web Services. All rights reserved.

Hit Counter