Answered by:
How to check referential integrity

Question
-
Hi there!
I have a database (MS SQL 2005) in which I have imported a lot of data. During the import I turned off the foreign key constraints. Is there a way to check, which rows in a table or across the whole database are violating the foreign key constraints? Is there any sql statement for this?
Thank you for your help!
Mike
Cya, MikeThursday, October 7, 2010 11:49 AM
Answers
-
ProIT-Mike,
When you say "turned off the foreign key constraints", do you mean that you disabled the constraints?
If so, then you could use "DBCC CHECKCONSTRAINTS" to check the integrity of a specified constraint or all constraints on a specified table in the current database.
Example:
USE tempdb; GO CREATE TABLE dbo.T1 ( c1 int NOT NULL PRIMARY KEY ); GO CREATE TABLE dbo.T2 ( c1 int NOT NULL IDENTITY(1, 1) PRIMARY KEY, c2 int NOT NULL CONSTRAINT FK_T2_T1 FOREIGN KEY REFERENCES dbo.T1(c1), ); GO ALTER TABLE dbo.T2 NOCHECK CONSTRAINT FK_T2_T1; GO INSERT INTO dbo.T2(c2) VALUES(6); GO DBCC CHECKCONSTRAINTS ('FK_T2_T1'); GO DROP TABLE dbo.T2, dbo.T1; GO
You can use sys.foreign_keys catalog view to check if the constraint is desabled, and also "ALTER TABLE" to enable it.
SELECT * FROM sys.foreign_keys WHERE parent_object_id = OBJECT_ID('dbo.T2') AND [name] = 'FK_T2_T1'; GO
AMB- Proposed as answer by Naomi N Thursday, October 7, 2010 2:10 PM
- Marked as answer by Kalman Toth Tuesday, October 12, 2010 12:52 PM
- Unmarked as answer by Kent Waldrop _ Tuesday, October 12, 2010 1:59 PM
- Marked as answer by Kent Waldrop _ Tuesday, October 12, 2010 4:32 PM
Thursday, October 7, 2010 2:09 PM
All replies
-
Select * from childTable where keyCol not in (select keyCol from parentTable)?
If the foreign keys have been disabled rather than dropped, and you are feeling a bit clever, you could write a script to cursor through sys.foreign_keys and generate some dynamic sql to do this for each fk individually.
Thursday, October 7, 2010 12:21 PM -
Try re-creating FK and you will see. Not aware of any built-in statement
Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/Thursday, October 7, 2010 12:51 PMAnswerer -
Try re-creating FK and you will see. Not aware of any built-in statement
Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/Thursday, October 7, 2010 12:52 PMAnswerer -
try this script...I think it should give......
declare @t table (ID int identity(1,1), tblName varchar(500)) insert into @t select distinct SP.name as ParentObject from sys.foreign_key_columns FK inner join sys.tables SP on FK.parent_object_id = SP.object_id inner join sys.tables SR on FK.referenced_object_id = SR.object_id declare @ID int = 1 declare @tablename varchar(500) while (@ID <= (select COUNT(*) from @t)) begin set @tablename = (select tblname from @t where ID = @ID) declare @sql nvarchar(max) set @sql = 'select P.* from ' + @tablename + ' P' + ( select ' left outer join ' + SR.name + ' on ' + SR.name + '.[' + SCR.name + '] = P.[' + SCP.name + '] ' from sys.foreign_key_columns FK inner join sys.tables SP on FK.parent_object_id = SP.object_id inner join sys.tables SR on FK.referenced_object_id = SR.object_id inner join sys.columns SCP on SCP.column_id = parent_column_id and SCP.object_id = FK.parent_object_id inner join sys.columns SCR on SCR.column_id = referenced_column_id and SCR.object_id = referenced_object_id where FK.parent_object_id = object_id(@tablename) for xml path('') ) + ' where ' + stuff( ( select 'or ' + SR.name + '.[' + SCR.name + '] is not null ' from sys.foreign_key_columns FK inner join sys.tables SP on FK.parent_object_id = SP.object_id inner join sys.tables SR on FK.referenced_object_id = SR.object_id inner join sys.columns SCP on SCP.column_id = parent_column_id and SCP.object_id = FK.parent_object_id inner join sys.columns SCR on SCR.column_id = referenced_column_id and SCR.object_id = referenced_object_id where FK.parent_object_id = object_id(@tablename) for xml path('') ),1,2,'') exec(@sql) set @ID = @ID + 1 end
Thursday, October 7, 2010 1:07 PM -
ProIT-Mike,
When you say "turned off the foreign key constraints", do you mean that you disabled the constraints?
If so, then you could use "DBCC CHECKCONSTRAINTS" to check the integrity of a specified constraint or all constraints on a specified table in the current database.
Example:
USE tempdb; GO CREATE TABLE dbo.T1 ( c1 int NOT NULL PRIMARY KEY ); GO CREATE TABLE dbo.T2 ( c1 int NOT NULL IDENTITY(1, 1) PRIMARY KEY, c2 int NOT NULL CONSTRAINT FK_T2_T1 FOREIGN KEY REFERENCES dbo.T1(c1), ); GO ALTER TABLE dbo.T2 NOCHECK CONSTRAINT FK_T2_T1; GO INSERT INTO dbo.T2(c2) VALUES(6); GO DBCC CHECKCONSTRAINTS ('FK_T2_T1'); GO DROP TABLE dbo.T2, dbo.T1; GO
You can use sys.foreign_keys catalog view to check if the constraint is desabled, and also "ALTER TABLE" to enable it.
SELECT * FROM sys.foreign_keys WHERE parent_object_id = OBJECT_ID('dbo.T2') AND [name] = 'FK_T2_T1'; GO
AMB- Proposed as answer by Naomi N Thursday, October 7, 2010 2:10 PM
- Marked as answer by Kalman Toth Tuesday, October 12, 2010 12:52 PM
- Unmarked as answer by Kent Waldrop _ Tuesday, October 12, 2010 1:59 PM
- Marked as answer by Kent Waldrop _ Tuesday, October 12, 2010 4:32 PM
Thursday, October 7, 2010 2:09 PM -
The script on the following page lists all FK to PK mappings including 'disabled' property:
http://www.sqlusa.com/bestpractices2005/foreignkeydefinition/
Kalman Toth, SQL Server & Business Intelligence Training; SQL 2008 GRAND SLAMTuesday, October 12, 2010 12:52 PM -
Please slow down. It looks to me like what is being asked for is a list of specific rows -- not just whether or not the FK is enabled / disabled. Maybe I am getting the question wrong, but I am not so sure the OP question has been answered.
Will someone please check me on this? Help please?
Tuesday, October 12, 2010 1:52 PM -
I think OP was asking the Orphan records, hope my proc. is giving same..Tuesday, October 12, 2010 1:54 PM
-
I think OP was asking the Orphan records
This is also the view I have of the OP. I am going to un-mark the answer for the time being; nothing personal, I just feel like we jumped the gun on this.
EDIT:
My Bad! Alejandro's answer was sufficient. Sorry for confusing this issue. I apprecated the subsequent responses from everyone and thank you for your understanding of my confusion.
- Edited by Kent Waldrop _ Tuesday, October 12, 2010 4:35 PM
Tuesday, October 12, 2010 1:58 PM -
The method provided by Alejandro (Hunchback) is robust albeit he did not provide a complete script.
Related BOL link:
DBCC CHECKCONSTRAINTS WITH ALL_CONSTRAINTS
GO
Kalman Toth, SQL Server & Business Intelligence Training; SQL 2008 GRAND SLAMTuesday, October 12, 2010 3:08 PM -
Please slow down. It looks to me like what is being asked for is a list of specific rows -- not just whether or not the FK is enabled / disabled. Maybe I am getting the question wrong, but I am not so sure the OP question has been answered.
Will someone please check me on this? Help please?
DBCC CHECKCONSTRAINTS will give you the list of the rows breaking the constraint. May be I should add more rows so we can see it.USE tempdb; GO CREATE TABLE dbo.T1 ( c1 int NOT NULL PRIMARY KEY ); GO CREATE TABLE dbo.T2 ( c1 int NOT NULL IDENTITY(1, 1) PRIMARY KEY, c2 int NOT NULL CONSTRAINT FK_T2_T1 FOREIGN KEY REFERENCES dbo.T1(c1), ); GO -- disable the constraint -- to be able to insert bad data ALTER TABLE dbo.T2 NOCHECK CONSTRAINT FK_T2_T1; GO INSERT INTO dbo.T1 VALUES(1); GO INSERT INTO dbo.T2(c2) VALUES(1); INSERT INTO dbo.T2(c2) VALUES(6); INSERT INTO dbo.T2(c2) VALUES(7); GO -- check the constraint DBCC CHECKCONSTRAINTS ('FK_T2_T1'); GO -- enable the constraint -- without checking if there are rows breaking it ALTER TABLE dbo.T2 WITH NOCHECK CHECK CONSTRAINT FK_T2_T1; GO -- check the constraint -- to see if there are rows breaking it DBCC CHECKCONSTRAINTS ('FK_T2_T1'); GO DROP TABLE dbo.T2, dbo.T1; GO Result: - First DBCC Table Constraint Where [dbo].[T2] [FK_T2_T1] [c2] = '6' [dbo].[T2] [FK_T2_T1] [c2] = '7' - Second DBCC Table Constraint Where [dbo].[T2] [FK_T2_T1] [c2] = '6' [dbo].[T2] [FK_T2_T1] [c2] = '7'
As you can see, the constraint can be checked no matter if it is enable or disable. You can use this approach to check any constraint in the table, or specific one. It will give you the list of rows breaking the constraint.
Disclaimer: I am not saying that my post is the answer the OP is looking for, since the question itself is not clear enough. the whole script is not only telling if the constraint is enable or not, it also tell you which key is breaking the constraint.
AMB
Tuesday, October 12, 2010 4:03 PM -
Thank you for picking me up, Alejandro.
Part of the reason I was willing to uncheck your answer was because I feel like we have a solid working relationship with each other. Your answers are typically mature and I figured you would take my response in stride and give good additional information. My experience has been that whenever I have asked for help you have willingly given it.
I like your work.
Kalman,
Thank you for bearing with me, too.
:)
Tuesday, October 12, 2010 4:25 PM -
That's a great tip. But I must say to MS, maker of SQL Server: When you've made the functionality to identify the rows, please consider reporting the information in a form suitable for programmatic consumption! While it's perfectly doable to extract the value 6 from the string [c2]='6', it's plain silly to waste cycles on doing so, knowing full well that the string was constructed from some in-memory table or other data structure holding precisely the piece of information we needed.
If DBCC CHECKCONSTRAINTS could have reported the "Where" column in the two parts it consists of (FK col and FK value) it would have been perfect for me, and I believe for the OP as well. I am also importing data, and need to handle the situation if there are integrity errors in the data coming in. Rather than just remove it I want to pinpoint the records in error and report back to where the data is coming from, so we can correct the root problem.
Tuesday, October 12, 2010 4:37 PM -
Nice tip Alejandro..... :) Probably my best learning point of today...Tuesday, October 12, 2010 4:46 PM
-
Kent,
I know you for long time now. I haven't had any doubt about your intentions in this or any other forum, other than being toward to help the OP.
Anyway, if I am wrong or my post is not what the OP is looking for, why not to uncheck it as the answer?
I would uncheck it myself. :))
AMB
Tuesday, October 12, 2010 4:57 PM -
Nice tip Alejandro..... :) Probably my best learning point of today...
Thanks for your kind words, ramireddy.One of the best thing about the forums is that they are a continue flow of tips, ideas, learning experience, etc.
AMB
Tuesday, October 12, 2010 4:59 PM -
That's a great tip. But I must say to MS, maker of SQL Server: When you've made the functionality to identify the rows, please consider reporting the information in a form suitable for programmatic consumption! While it's perfectly doable to extract the value 6 from the string [c2]='6', it's plain silly to waste cycles on doing so, knowing full well that the string was constructed from some in-memory table or other data structure holding precisely the piece of information we needed.
If DBCC CHECKCONSTRAINTS could have reported the "Where" column in the two parts it consists of (FK col and FK value) it would have been perfect for me, and I believe for the OP as well. I am also importing data, and need to handle the situation if there are integrity errors in the data coming in. Rather than just remove it I want to pinpoint the records in error and report back to where the data is coming from, so we can correct the root problem.
Not a bad idea!You can enter the suggestion in http://connect.microsoft.com/SQLServer. If you do it, post it back here, so others can vote for it.
Keep in mind that the foreign key could be a multi columns one.
AMB
Tuesday, October 12, 2010 5:03 PM