THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Alexander Kuznetsov

Be Aware of These Loopholes in Your Referential Integrity

I am going to discuss several potential problems with referential integrity, because they keep coming up in the newsgroups.

 

Parent and Child Tables Are in Different Databases.

 

Although you cannot use a foreign key in this situation, there are workarounds – you can use either triggers or UDFs wrapped in check constraints. Either way, your data integrity is not completely watertight: if the database with your parent table crashes and you restore it from a backup, you may easily end up with orphans.

 

Parent-Child Relationship Is Enforced by Triggers.

 

There are quite a few situations when triggers do not fire, such as:

·         A table is dropped.

·         A table is truncated.

·         Settings for nested and/or recursive triggers prevent a trigger from firing.

Also a trigger may be just incorrect. Either way, you may end up with orphans in your database.

 

Parent-Child Relationship Is Enforced by UDFs Wrapped in Check Constraints.

 

Such “constraints” do not prevent from deleting parent rows. Again, you may end up with orphans in your database.

In most cases the easiest and simplest way to enforce a parent-child relationship is to use a built-in tool – do not reinvent the wheel, have both tables in one database and use a foreign key.

Published Tuesday, May 13, 2008 6:17 PM by Alexander Kuznetsov

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Gregor Dzierzon said:

I agree that it is best to put all related tables in the same database. That assumes however, that it is always possible to move all related tables into one database. Many organizations (for multiple reasons) have multiple databases. Of course it is possible to copy an entire table and maintain the data in multiple locations, but in my experience this causes much bigger problems than the downsides of triggers that you mentioned above.

You can further minimize the risks of triggers/udfs by maintaining triggers/udfs on both databases. One to check for parental constraint, the other for checking child constraints. This is still not ideal, but it is useful when maintaining cross-database RI is required.

July 14, 2008 2:57 PM
 

maurice said:

I don’t agree with this idea of using a single database for everything.

For example, I’m working on an ERP project with 7 modules, and we need to consider

- Security issue

- Performance issue

It is easier for me to design separated database for HQ, Payroll, Purchase order, and create user for each database. Users of payroll don’t need to access Purchase Order database and so on.

With different databases, we have different log files, and this makes the database faster

If I’m working on payroll, I make a backup of payroll only; I don’t need to make backup for all the databases,

@dgm

November 19, 2009 10:42 AM
 

Alex Kuznetsov said:

Maurice,

We can grant permissions to schemas or have even finer level of granularity. If users do not have permissions for a schema or an object, they just do not see it. There is no need to have separate databases to enforce security. Similarly, in Enterprise Edition there are built in ways to partition and scale up without splitting a database.

On the other hand, if you ever have to restore two or more databases with parent-child relationships across database boundaries, you will have to take care of orphan rows. You will not want to do it again once you have experienced it for yourself.

November 20, 2009 11:27 PM
 

Sam said:

Hello,

I am beginner. Can you please tell me how referential integrity can be enforced between tables in different schemas on one database. Can this be done using erwin?

April 15, 2011 2:29 PM
 

Babak said:

Agree with Alex. Basically the DB designer should not decide which entity goes into same DB. The problem itself tells what entity should or shouldn't be together in the same DB. In other words it's not upto DB designer but rather upto to the purpose of the design. Of course DB designer uses his/her knowledge to identify and to define entities but this should be only if he/she is sure they belong together. Now the question is how we use data that are in different DBs, refering to Maurice's case. The answer is you want to pouplate the same dimensions separately for each DBs. Again, two DBs needs same dimension, then populate the dimensions as if they are representting different entity even though in real world they don't. I referring to dimensions not the facts because facts must not at all be shared between DBs, it's terrible design otherwise.

August 12, 2011 7:10 PM

Leave a Comment

(required) 
(required) 
Submit

About Alexander Kuznetsov

Alex Kuznetsov has been working with object oriented languages, mostly C# and C++, as well as with databases for more than a decade. He has worked with Sybase, SQL Server, Oracle and DB2. He regularly blogs on sqlblog.com, mostly about database unit testing, defensive programming, and query optimization. Alex has written a book entitled "Defensive Database Programming with Transact-SQL" and several articles on simple-talk.com and devx.com. Currently he works at DRW Trading Group in Chicago, where he leads a team of developers, practicing agile development, defensive programming, TDD, and database unit testing.

This Blog

Syndication

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement