SQL Server Foreign Keys – Some of the Mystery Explained! (Part 2)
Note: This is Part 2 of a two-part article on SQL Server foreign keys. Read Part 1 here.
A recap of Part 1: some issues with disabling foreign keys
In Part 1 of this article we examined the relationship of parent and child tables for Foreign Keys and that they maintain data consistency between the two tables. However, we also learned that when you disable a Foreign Key (there are valid reasons for this) and then re-enable the same Foreign Key that the is_not_trusted attribute may still be set to not trusted.
You may want to disable Foreign Keys when doing large data inserts. If the Foreign Key is enabled, each row inserted has to perform a constraint check on each Foreign Key defined for the parent table on the respective child table – this can considerably slow down data insertion. This is commonly done for data bulk inserts and/or ETL jobs. By disabling the Foreign Key, the data insertion process can be speeded up, but at the risk of data inconsistency. If there is data inconsistency, it will be discovered (with an error message) when you try to re-enable the Foreign Key and it’s “is trusted” property.
How do you re-enable a foreign key?
And now let’s answer the question generated at the end of Part 1 of this article on how to get the foreign key back to enabled and trusted. As we saw in Part 1, you can re-enable a foreign key with
ALTER TABLE dbo.Person CHECK CONSTRAINT FK_Person_AddressID
This re-enables the Foreign Key and sets the is-disabled attribute back to zero, but leaves the is_not_trusted attribute = 1. Essentially the above statement (while fast) re-enables the constraint for future data modification/insert, but does not check the data already in the table – hence the is_not_trusted attribute = 1. The proper syntax to both re-enable the Foreign Key and re-perform a data consistency check on the existing table is
ALTER TABLE dbo.Person WITH CHECK CHECK CONSTRAINT FK_Person_AddressID
Yes, it looks like Microsoft is stuttering but that is the correct syntax. The WITH CHECK clause forces a check on the existing data and the second CHECK re-enables the Foreign Key. So now when we run the query:
SELECT is_disabled, is_not_trusted FROM [sys].[foreign_keys] WHERE name = 'FK_Person_AddressID';
and if the data already in the table is consistent we get no error on the above Alter table and the is_disabled attribute = 0 and the is_not_trusted attribute = 0. Be aware that the check on all the existing data may be very slow for large tables.
If the table data is inconsistent (say by running following
ALTER TABLE dbo.Person NOCHECK CONSTRAINT FK_Person_AddressID
INSERT dbo.Person (LastName,FirstName,AddressID)
SELECT 'Jefferson','Thomas',20
we get one row inserted (with no corresponding AddressID = 20 in dbo.Address)).
And if we try and run:
ALTER TABLE dbo.Person WITH CHECK CHECK CONSTRAINT FK_Person_AddressID
we get the error message:
Msg 547, Level 16, State 0, Line 1 The ALTER TABLE statement conflicted with the FOREIGN KEY constraint "FK_Person_AddressID". The conflict occurred in database "Test", table "dbo.Address", column 'ADDRESSID'.
Notice though that we could run:
ALTER TABLE dbo.Person CHECK CONSTRAINT FK_Person_AddressID
without error, but the is_not_trusted attribute = 1.
So why all the fuss about the is_not_trusted attribute? First, when it = 0 then we know there is data consistency between the parent and child table. Secondly, when it = 0, the query optimizer can take advantage of the attribute and not perform data consistency checks on the child table. This may result in less IOs and better performance.
How to find foreign keys
A quick and dirty way to indirectly find child tables that have foreign key relationships tied to them is to use Microsoft SQL Server Management Studio (SSMS). Right click on the table name in SSMS and select “View Dependencies”. This will give you a window something like
Note the Person table is displayed and if you go to the Person table in SSMS and check its Foreign Keys, you will find a Foreign Key there with a child table of dbo.Address.
However, I prefer a more programmatic way of finding Foreign Keys pointing to a specific child table. In the code listed at the end of this article I have defined a stored procedure dbo.GetFKsByTable with parameters of @SchemaName and @TableName. Before calling the stored procedure, you must first define a temporary table #tFK like
CREATE TABLE #tFK (
SchemaName SysName,
TableName SysName,
DropStatement NVARCHAR(4000),
CreateStatement NVARCHAR(4000))
The stored procedure then uses the appropriate dynamic management views (DMVs) to find all related Foreign Keys that point to the child table (as designated in the parameters) whether they are enabled or disabled. It then uses dynamic SQL to generate the appropriate DROP and CREATE statements for the reference table. The stored procedure is relatively simplistic and does not address all the possible attributes to perfectly recreate all Foreign Keys, but it works for my purposes. Also note that the stored procedure will also handle multi-column Foreign Keys (hence the XML code).
For the dbo.Address table discussed in Parts 1 and 2 of this article, it generates the following code:
ALTER TABLE dbo.Person DROP CONSTRAINT [FK_Person_AddressID];
and
ALTER TABLE dbo.Person WITH CHECK ADD CONSTRAINT [FK_Person_AddressID] FOREIGN KEY (AddressID)
REFERENCES [dbo].[Address] (ADDRESSID)
ALTER TABLE dbo.Person CHECK CONSTRAINT [FK_Person_AddressID];
Hopefully you now have a better insight into Foreign Keys and their usefulness and how to maintain them. Any questions or discussion, please email.
Stored Procedure: Get Foreign Keys by Table
IF OBJECT_ID(N'dbo.csp_GetFKsByTable') IS NOT NULL DROP PROCEDURE dbo.csp_GetFKsByTable
GO
CREATE PROCEDURE dbo.csp_GetFKsByTable (
@SchemaName SYSNAME,
@TableName SYSNAME )
AS
/*
csp_GetFKsByTable
by MByrd, 20111221
Stored proc puts drop and create dependency Foreign Key (FK) statements into following table
(build by calling script):
CREATE TABLE #tFK (SchemaName SysName, TableName SysName, DropStatement NVARCHAR(4000), CreateStatement NVARCHAR(4000))
Requires input parameter of @SchemaName,@TableName.
Test:
IF OBJECT_ID(N'tempdb..#tFK') IS NOT NULL DROP TABLE #tFK
CREATE TABLE #tFK (SchemaName SysName, TableName SysName, DropStatement NVARCHAR(4000), CreateStatement NVARCHAR(4000))
EXEC dbo.csp_GetFKsByTable 'dbo','Address'
SELECT * FROM #tFK
*/
DECLARE @FKName SysName,
@ParentSchema SysName,
@ParentTable SysName,
@ParentColumn SysName,
@RefSchema SysName,
@RefTable SysName,
@RefColumn SysName,
@Is_Disabled bit,
@FKDropSQL NVARCHAR(4000),
@FKCreateSQL NVARCHAR(4000);
SELECT DISTINCT fk.name FKName, s.name ParentSchema, o.name ParentTable, c.name ParentColumn,s2.name RefSchema,o2.name RefTable,c2.name RefColumn, fk.is_disabled, c2.column_id RefTableColumnID
INTO #t
FROM sys.foreign_keys fk
JOIN sys.objects o
ON o.object_id = fk.parent_object_id
JOIN sys.schemas s
on s.schema_id = o.schema_id
JOIN sys.objects o2
on o2.object_id = fk.referenced_object_id
JOIN sys.schemas s2
on s2.schema_id = o2.schema_id
JOIN sys.foreign_key_columns fkc
ON fkc.parent_object_id = fk.parent_object_id
AND fkc.referenced_object_id = fk.referenced_object_id
JOIN [INFORMATION_SCHEMA].[CONSTRAINT_COLUMN_USAGE] ccu
ON ccu.TABLE_NAME = o.name
AND ccu.CONSTRAINT_NAME = fk.name
JOIN sys.columns c
ON c.column_id = fkc.parent_column_id
AND c.object_id = o.object_id
AND c.column_id = fkc.parent_column_id
AND c.name = ccu.COLUMN_NAME
JOIN sys.columns c2
ON c2.column_id = fkc.referenced_column_id
AND c2.object_id = o2.object_id
AND c2.column_id = fkc.referenced_column_id
WHERE s2.name = @SchemaName
AND o2.name = @TableName
ORDER BY 1,2,RefTableColumnID
DECLARE cFK CURSOR FOR
select FKName,ParentSchema,ParentTable,
SUBSTRING( ( SELECT ( ',' + ParentColumn)
FROM #t t2
WHERE t1.FKName = t2.FKName
AND t1.ParentSchema = t2.ParentSchema
AND t1.ParentTable = t2.ParentTable
ORDER BY FKName,ParentSchema,ParentTable,/*ParentColumn,*/RefTableColumnID
FOR XML PATH ('')),2,1000 ) ParentColumn,
RefSchema,RefTable,
SUBSTRING( ( SELECT ( ',' + RefColumn)
FROM #t t4
WHERE t1.FKName = t4.FKName
AND t1.RefSchema = t4.RefSchema
AND t1.RefTable = t4.RefTable
AND t1.ParentSchema = t4.ParentSchema
ORDER BY FKName,RefSchema, RefTable,RefTableColumnID
FOR XML PATH ('')),2,1000 ) RefColumn,
is_disabled
FROM #t t1
GROUP BY FKName,ParentSchema,ParentTable,RefSchema,RefTable,is_disabled
OPEN cFK
FETCH NEXT FROM CFK INTO @FKName,@ParentSchema,@ParentTable,@ParentColumn,@RefSchema, @RefTable,@RefColumn,@Is_Disabled
WHILE (@@FETCH_STATUS = 0)
BEGIN
SET @FKDropSQL = N'ALTER TABLE ' + @ParentSchema + '.' + @ParentTable + N' DROP CONSTRAINT [' + @FKName + '];';
IF @Is_Disabled = 1
BEGIN
SET @FKCreateSQL = N'ALTER TABLE ' + @ParentSchema + '.' + @ParentTable + N' WITH NOCHECK ADD CONSTRAINT [' + @FKName + '] FOREIGN KEY (' + @ParentColumn + ')
REFERENCES [' + @RefSchema + '].[' + @RefTable + '] (' + @RefColumn + ')
ALTER TABLE ' + @ParentSchema + '.' + @ParentTable + N' NOCHECK CONSTRAINT [' + @FKName + '];';
END
ELSE
BEGIN
SET @FKCreateSQL = N'ALTER TABLE ' + @ParentSchema + '.' + @ParentTable + N' WITH CHECK ADD CONSTRAINT [' + @FKName + '] FOREIGN KEY (' + @ParentColumn + ')
REFERENCES [' + @RefSchema + '].[' + @RefTable + '] (' + @RefColumn + ')
ALTER TABLE ' + @ParentSchema + '.' + @ParentTable + N' CHECK CONSTRAINT [' + @FKName + '];';
END
--PRINT @FKDropSQL;
--PRINT @FKCreateSQL;
INSERT #tFK
SELECT @SchemaName,@TableName,@FKDropSQL,@FKCreateSQL;
FETCH NEXT FROM CFK INTO @FKName,@ParentSchema,@ParentTable,@ParentColumn, @RefSchema,@RefTable,@RefColumn,@Is_Disabled;
END
CLOSE cFK;
DEALLOCATE cFK;
RETURN 0;
GO
by Mike Byrd, ByrdNest Consulting, SQL Server expert DBA, developer, instructor, and speaker.
[…] Note: This is Part 1 of a two-part article on SQL Server foreign keys You may read Part 2 here. […]