none
How to check referential integrity RRS feed

  • 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
    Thursday, 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

    Some guidelines for posting questions...

    Thursday, October 7, 2010 2:09 PM
    Moderator

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 PM
    Answerer
  • 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 PM
    Answerer
  • 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

    Some guidelines for posting questions...

    Thursday, October 7, 2010 2:09 PM
    Moderator
  • 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
    Tuesday, October 12, 2010 12:52 PM
    Moderator
  • 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
    Moderator
  • 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.

    Tuesday, October 12, 2010 1:58 PM
    Moderator
  • 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
    Tuesday, October 12, 2010 3:08 PM
    Moderator
  • 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...

    Tuesday, October 12, 2010 4:03 PM
    Moderator
  • 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
    Moderator
  • 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

    Some guidelines for posting questions...

    Tuesday, October 12, 2010 4:57 PM
    Moderator
  • 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...

    Tuesday, October 12, 2010 4:59 PM
    Moderator
  • 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...

    Tuesday, October 12, 2010 5:03 PM
    Moderator