THE SQL Server Blog Spot on the Web

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

John Paul Cook

Finding Tables Without Referential Integrity

Following up on yesterday's post from Louis, sometimes you encounter databases with missing referential integrity constraints. Even when the database is intended to have RI, accidents happen. Maybe a script dropped RI constraints before a bulk load and didn't quite get all of the constraints recreated. Whatever the reason, it's good to have a script to identify tables that are not referenced and do not reference other tables. Here's a script that works on SQL Server 2005 and 2008 (updated to no longer use sysreferences as per Adam Machanic's suggestion): 

SELECT sys.objects.name
FROM sys.objects
LEFT JOIN sys.foreign_keys referenced
ON sys.objects.object_id = referenced.referenced_object_id
LEFT JOIN sys.foreign_keys parent
ON sys.objects.object_id = parent.parent_object_id
WHERE sys.objects.type = 'U'
AND referenced.referenced_object_id IS NULL
AND
parent.parent_object_id IS NULL

Louis did a good job explaining the importance of using the database engine to protect the integrity of the data. As a consultant, I've seen lots of databases with varying degrees of referential integrity. It never works out well in the long run when referential integrity isn't enforced in the database. Application code - even if it is christened object relational mapping - is no substitute for imposing referential integrity checks in the database. A relational database is more than a data storage repository - and it should be.

 

Published Wednesday, April 08, 2009 1:38 PM by John Paul Cook
Filed under:

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

 

jamiet said:

Hi John,

Very useful, thank you very much.

I wonder if  could ask a favour. Would you be willing to join my online SQL code repository and submit this snippet to it? You can read up about the repository at http://blogs.conchango.com/jamiethomson/archive/2009/01/20/sql-server-code-repository-on-live-mesh.aspx

Failing that, would you permit me to take the snippet and put it into the repository myself? I'll attribute it to you of course and link back here.

Thanks

Jamie Thomson

April 8, 2009 3:00 PM
 

John Paul Cook said:

Code snippets I post here are freely redistributable with or without attribution. Attribution is appreciated as long as the code works! If I ever post code that doesn't work, please don't attribute it to me!

April 8, 2009 3:51 PM
 

jamiet said:

Cool, thanks John. I've added it to the repository.

April 9, 2009 4:18 AM
 

DavidStein said:

Pardon my ignorance but I wanted to verify this.  The list of tables that is returned by this script is the list of tables that have no referential integrity to any tables in the database?  

April 9, 2009 12:02 PM
 

John Paul Cook said:

Yes, David, the script returns a list of tables that neither reference other tables nor are referenced by other tables. They are neither children nor parents. Sometimes tables like these are errors caused by a dropped foreign key relationship.

April 9, 2009 1:11 PM
 

DavidStein said:

Well, the reason I ask is because when I run that script against my ERP Software Database, every single table shows up.  

Not a good sign.  

April 9, 2009 2:25 PM
 

John Paul Cook said:

It is quite common for large software packages such as ERPs and CRMs to enforce all constraints and referential integrity through code. Packages that run on all major relational database platforms are much more easy to port between different database platforms when none of the data integrity features of the database are used. As long as the data is always accessed through the application code and there aren't any bugs in the application code, there won't be any problems with the data.

April 9, 2009 3:18 PM
 

Madhivanan said:

John Paul Cook has posted in his blog to show the list of tables that have no Referential Integrity.

April 10, 2009 10:42 AM
 

drsql said:

I must need more code to get all of the comments :)  And definitely missing constraints are a big problem.  Sometimes over the course of dropping and adding them you lose a few if you aren't careful.

I use a comparison tool and my model database to compare and make sure that nothing is lost during upgrades lately, but I used to have this problem quite often.

April 11, 2009 12:15 AM
 

RowlandG said:

Enjoyed your post -- thanks!

July 21, 2009 8:11 AM

Leave a Comment

(required) 
(required) 
Submit

About John Paul Cook

John Paul Cook is both a Registered Nurse and a Microsoft SQL Server MVP experienced in Microsoft SQL Server and Oracle database application design, development, and implementation. He has spoken at many conferences including Microsoft TechEd and the SQL PASS Summit. He has worked in oil and gas, financial, manufacturing, and healthcare industries. Experienced in systems integration and workflow analysis, John is passionate about combining his IT experience with his nursing background to solve difficult problems in healthcare. He sees opportunities in using business intelligence and Big Data to satisfy healthcare meaningful use requirements and improve patient outcomes. John graduated from Vanderbilt University with a Master of Science in Nursing Informatics and is an active member of the Sigma Theta Tau nursing honor society. Contributing author to SQL Server MVP Deep Dives and SQL Server MVP Deep Dives Volume 2.

This Blog

Syndication

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