By: Eli Leiba | Updated: 2011-03-16 | Comments (2) | Related: 1 | 2 | 3 | 4 | 5 | > 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.
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
- Create the procedure in your application database and test it to see if you can find any FK issues.
- Here are some related tips:
About the author
Eli Leiba is a senior application DBA, a teacher and a senior database consultant with 19 years of RDBMS experience.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
Article Last Updated: 2011-03-16
