Oracle Consulting Oracle Training Oracle Support Development
Home
Catalog
Oracle Books
SQL Server Books
IT Books
Job Interview Books
eBooks
Rampant Horse Books
911 Series
Pedagogue Books

Oracle Software
image
Write for Rampant
Publish with Rampant
Rampant News
Rampant Authors
Rampant Staff
 Phone
 800-766-1884
Oracle News
Oracle Forum
Oracle Tips
Articles by our Authors
Press Releases
SQL Server Books
image
image

Oracle 11g Books

Oracle tuning

Oracle training

Oracle support

Remote Oracle

STATSPACK Viewer

Privacy Policy

 

   
  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  

Linux Oracle commands syntax poster

ION Oracle tuning software

Oracle data dictionary reference poster



Oracle Forum

BC Oracle consulting support training

BC remote Oracle DBA   

 

   

 Copyright © 1996 -2016 by Burleson. All rights reserved.


Oracle® is the registered trademark of Oracle Corporation. SQL Server® is the registered trademark of Microsoft Corporation. 
Many of the designations used by computer vendors to distinguish their products are claimed as Trademarks
 

Hit Counter