SQL Server Foreign Keys: Some Mystery Explained! (Part 2)

on November 12, 2012


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

Foreign Key Relationship Dialog

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.

Related Posts

Trackbacks

Leave a Reply