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, Mike

Answer 1

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.

 

Answer 2

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/

Answer 3

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/

Answer 4

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

Answer 5

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

Some guidelines for posting questions...

Answer 6

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 SLAM

Answer 7

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?

 

Answer 8

I think OP was asking the Orphan records, hope my proc. is giving same..

Answer 9

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.

Answer 10

The method provided by Alejandro (Hunchback) is robust albeit he did not provide a complete script.

Related BOL link:

C. Check all enabled and disabled constraints on all tables
This example checks the integrity of all enabled and disabled constraints on all tables in the current database.

DBCC CHECKCONSTRAINTS WITH ALL_CONSTRAINTS
GO 

---

Kalman Toth, SQL Server & Business Intelligence Training; SQL 2008 GRAND SLAM

Answer 11

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

Some guidelines for posting questions...

Answer 12

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.

:)

Answer 13

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.

Answer 14

Nice tip Alejandro..... :)  Probably my best learning point of today...

Answer 15

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

Some guidelines for posting questions...

Answer 16

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

Some guidelines for posting questions...

Answer 17

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

Some guidelines for posting questions...