Learn more about SQL Server tools

   
   






















































Connect with MSSQLTips




MSSQLTips Facebook Page MSSQLTips LinkedIn Page MSSQLTips RSS Feed MSSQLTips Pinterest Page MSSQLTips Twitter Page MSSQLTips Google+ Page

Find Violating SQL Server Foreign Key Values

MSSQLTips author Eli Leiba By:   |   Read Comments (2)   |   Related Tips: 1 | 2 | 3 | 4 | 5 | 6 | More > Constraints
Problem

In many databases, foreign key constraints are not used initially and then later created on tables after data already exists. If data already exists and there are possible foreign key violations the constraint may be created using the NOCHECK option. In addition, sometimes when loading BULK data, foreign keys are disabled or removed to make the load faster. Because of these issues you may have orphan data in your database tables. There are ways to check this using NOT IN or NOT EXISTS queries, but in this tip we look at another method and a stored procedure built around this method to find records in violation.

Solution

The solution I'm suggesting is to create a stored procedure called usp_Find_Non_Integrity_FK_Vals. This procedure takes parameters for the parent and child table names along with the parent and child schema names and then determines if there are any issues. The procedure looks up the foreign key constraint name by querying the INFORMATION_SCHEMA views and then executes DBCC CHECKCONSTRAINTS. The DBCC CHECKCONSTRAINTS statement is a special statement that checks the integrity of a specified constraint or all constraints on a specified table in the current database. In addition, the procedure formats the output a little different versus the standard output from DBCC CHECKCONSTRAINTS.

Here is the stored procedure.

CREATE PROCEDURE dbo.usp_Find_Non_Integrity_FK_Vals
(@ParentSchemaName SYSNAME = 'dbo',
@ParentTableName SYSNAME,
@ChildSchemaName SYSNAME = 'dbo',
@ChildTableName SYSNAME)
AS
BEGIN
DECLARE
@tsql VARCHAR(300)
DECLARE @FKconstrName VARCHAR(50)

SET NOCOUNT ON

-- get the foreign key constraint name from parent and child table names
SELECT @FKconstrName = a.CONSTRAINT_NAME
FROM
INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS a,
INFORMATION_SCHEMA.KEY_COLUMN_USAGE b1,
INFORMATION_SCHEMA.KEY_COLUMN_USAGE b2
WHERE a.UNIQUE_CONSTRAINT_NAME = b1.CONSTRAINT_NAME AND
a.UNIQUE_CONSTRAINT_CATALOG = b1.CONSTRAINT_CATALOG AND
a.UNIQUE_CONSTRAINT_SCHEMA = b1.CONSTRAINT_SCHEMA AND
a.CONSTRAINT_NAME = b2.CONSTRAINT_NAME AND
a.CONSTRAINT_CATALOG = b2.CONSTRAINT_CATALOG AND
a.CONSTRAINT_SCHEMA = b2.CONSTRAINT_SCHEMA AND
b1.TABLE_NAME = @ParentTableName AND
b2.TABLE_NAME = @ChildTableName AND
b1.CONSTRAINT_SCHEMA = @ParentSchemaName AND
b2.CONSTRAINT_SCHEMA = @ChildSchemaName

-- construct a DBCC CHECKCONSTRAINTS TSQL
SET @tsql = 'DBCC CHECKCONSTRAINTS (' + '''' + @FKconstrName + '''' + ')'

CREATE TABLE ##tmp (TName SYSNAME,
constName SYSNAME,
whrClause VARCHAR(1000))

-- EXEC TSQL Dynamically and get all integrity FK problems
INSERT ##tmp EXEC (@tsql)

-- output the results
SELECT SUBSTRING (whrClause,1,
CHARINDEX ('=',whrClause,1) - 1) AS FKcol,
REPLACE (SUBSTRING (whrClause,
CHARINDEX ('=',whrClause,1) +1,
LEN (whrClause) - CHARINDEX ('=',whrClause,1) - 1 ),'''','') AS FKval
FROM ##tmp

-- drop temporary table
DROP TABLE ##tmp

END
GO

To test this, I have created a simple example to illustrate this issue.

Let's say we have these two tables.

  • DBPlatform - a table consisting of different RDMBS platforms such as MSSQL and ORACLE
  • Applications - a table consisting of various applications that run on these RDBMS platforms

After the tables are created we add some data to both tables.

After the data was inserted we create a foreign key (FK) between the two tables. To make this example work we used the NOCHECK option when we created the FK. This will allow us to create the foreign key even if there are foreign key violations.

Here is the T-SQL example script.

CREATE TABLE DBPlatform (Fid INT IDENTITY NOT NULL, Fdesc CHAR(8))
GO

ALTER TABLE DBPlatform ADD CONSTRAINT PK_DBPlatform PRIMARY KEY (Fid)
GO

INSERT INTO DBPlatform VALUES ('MSSQL')
INSERT INTO DBPlatform VALUES ('ORACLE')
GO

CREATE TABLE Applications ([Sid] INT IDENTITY NOT NULL, Fid INT,Sdesc CHAR(20))
GO

ALTER TABLE Applications ADD CONSTRAINT PK_Application PRIMARY KEY ([Sid] )
GO

INSERT INTO Applications VALUES (1,'SALES')
INSERT INTO Applications VALUES (3,'ACCOUNTS')
INSERT INTO Applications VALUES (2,'BILLING')
INSERT INTO Applications VALUES (2,'DWH')
GO

ALTER TABLE Applications WITH NOCHECK
ADD CONSTRAINT
FK_Applications_DBPlatform FOREIGN KEY (Fid) REFERENCES DBPlatform (Fid)
GO

/* Now, In order to show the violating rows, execute the following */
EXEC usp_Find_Non_Integrity_FK_Vals @ParentTableName = 'DBPlatform', @ChildTableName = 'Applications'
GO

SELECT * FROM Applications WHERE Fid = 3
GO

Below is the output after running the stored procedure to find any foreign key violations.

The first result set shows the foreign key column and the value that is in violation.

The second result set shows the detail line of our record in violation.

a simple way to find foreign key violations in sql server 2008 and 2005

That's all there is to it. A simple way to find foreign key violations. Please note that the procedure was tested on both SQL Server 2008 and 2005.

Next Steps


Last Update: 3/16/2011


About the author
MSSQLTips author Eli Leiba
Eli Leiba is a senior application DBA, a teacher and a senior database consultant with 19 years of RDBMS experience.

View all my tips


print tip Print  
Become a paid author





join MSSQLTips for free SQL Server tips     



Learn more about SQL Server tools
Post a comment or let the author know this tip helped you.

       All comments are reviewed, so stay on subject or we may delete your comment.

*Name   *Email Notify for updates



       Note: your email address is not published. Required fields are marked with an asterisk (*)


Get free SQL tips:

*Enter Code refresh code     



Wednesday, March 16, 2011 - 2:31:27 PM - Eli Leiba Read The Tip

The Procedure is designed simply for one FK between tables

In order to have more than one consatraint, you should change the select to use a cursor and run DBCC CHECKCONSTRAINTS

IN the cusor loop for each constraint found.

 

Eli


Wednesday, March 16, 2011 - 7:34:26 AM - Dick at Pershing Read The Tip

it is possible to have more than one FK constraint between tables, so this statement would be non-deterministic

SELECT @FKconstrName = a.CONSTRAINT_NAME FROM ...

so IMHO you should either use a TOP 1 construct (and ignore subsequent ones), or use a cursor

 



 
Sponsor Information