THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Louis Davidson

Creating FOREIGN KEY constraints as enabled, trusted, non-trusted, and/or disabled

I am in the middle of building a utility (for work, and for my next SQLBLOG post), that will help when you need to drop the foreign key constraints on a table, but then replace them without having to save off the script manually. Part of the process is to generate the script for the foreign key constraint, so the first thing I need to do is to make sure I have all of the scripting possibilities understood.

When I started hunting around to remember how to create a disabled constraint, I couldn't easily find anything, so I figures I would make this a two-parter. (My blogging rule is if I look for something and find a good article about it, reference it, then tweet the article out. If it is too hard to find, blog about it!) So today I will review how to create a FOREIGN KEY constraint in three ways:

  • Enabled, and Trusted - Just as you would normally create one
  • Enabled, Not Trusted - The "quick" way, not checking data to see if any wrong data already exists, but not allowing new, bad data in
  • Disabled, Not Trusted - The constraint is basically documentation of the relationship, but you are on your own to make sure the data matches the constraint

First, create a couple of tables that can be used for the demonstration, and then show how to create the constraints as trusted, non-trusted, and disabled. (Note: CHECK constraints are very similar in the ways you can set them to the same three states, if you are needing to do the same for CHECKs).

--Here is the script that will build the database, and then recreate the objects

CREATE DATABASE TestRebuildConstraints;
GO
USE TestRebuildConstraints;
GO
--a safeguard against building stuff in master is to never use dbo schema, and
--then create the schema only in the db you are expecting to
IF DB_ID() = DB_ID('TestRebuildConstraints')
    EXEC ('CREATE SCHEMA Demo');
GO

Then I will create a set of tables (which can be dropped and recreated over and over as you test different configurations;

--From here, you can rerun if you want to try the code:
DROP TABLE IF EXISTS demo.ChildTable, demo.ParentTable, demo.GrandParentTable;

CREATE TABLE Demo.GrandParentTable
(
    GrandParentTableId    INT CONSTRAINT PKGrandParentTable PRIMARY KEY
)

CREATE TABLE Demo.ParentTable
(
    ParentTableId    INT CONSTRAINT PKParentTable PRIMARY KEY,
    GrandParentTableId INT
)

CREATE TABLE Demo.ChildTable
(
    ChildTableId    INT CONSTRAINT PKChildTable PRIMARY KEY,
    ParentTableId INT
)
GO

Enabled, and Trusted

To create your constraint enabled and trusted, it is the base, simple ALTER command, telling the column in the child table that references the parent (this could also be a part of the CREATE TABLE statement.)

ALTER TABLE Demo.ChildTable
    ADD CONSTRAINT ChildTable$ref$ParentTable
        FOREIGN KEY (ParentTableId) REFERENCES Demo.ParentTable(ParentTableId);


ALTER TABLE Demo.ParentTable
    ADD CONSTRAINT ParentTable$ref$GrandParentTable
        FOREIGN KEY (GrandParentTableId) REFERENCES Demo.GrandParentTable(GrandParentTableId);

GO

The data in the table will be checked, and as such the constraints will be trusted, and enabled. It never hurts to check your constraint's status using the system catalog views:

SELECT is_not_trusted, is_disabled, name
FROM sys.foreign_keys
WHERE OBJECT_SCHEMA_NAME(object_id) = 'Demo';

is_not_trusted is_disabled name
-------------- ----------- -----------------------------------
0              0           ChildTable$ref$ParentTable
0              0           ParentTable$ref$GrandParentTable

Enabled, Not Trusted

Now, if you want to create the constraints as not trusted, not checking any existing data, you use WITH NOCHECK option on the ALER TABLE statement:

--Drop the existing constraint for the demo
ALTER TABLE Demo.ChildTable
    DROP CONSTRAINT ChildTable$ref$ParentTable;

--Add the constraint:
ALTER TABLE Demo.ChildTable
    WITH NOCHECK --<<< Added this
        ADD CONSTRAINT ChildTable$ref$ParentTable
            FOREIGN KEY (ParentTableId) REFERENCES Demo.ParentTable(ParentTableId);

Now, check the system catalog, and the ChildTable$ref$ParentTable constraint is not trusted, but it is enabled:

SELECT is_not_trusted, is_disabled, name
FROM sys.foreign_keys
WHERE OBJECT_SCHEMA_NAME(object_id) = 'Demo';

is_not_trusted is_disabled name
-------------- ----------- -----------------------------------
1              0           ChildTable$ref$ParentTable
0              0           ParentTable$ref$GrandParentTable


Disabled, Not Trusted

This is where things were kind of confusing in the syntax. Disabling a constraint is pretty straightforward (though slightly confusing with the NOCHECK showing up again with a slightly different meaning:

ALTER TABLE Demo.ParentTable
    NOCHECK CONSTRAINT ParentTable$ref$GrandParentTable; 
GO 

You can see in the metadata that it is not not trusted nor enabled:

SELECT is_not_trusted, is_disabled, name
FROM sys.foreign_keys
WHERE OBJECT_SCHEMA_NAME(object_id) = 'Demo';

is_not_trusted is_disabled name
-------------- ----------- -----------------------------------
1              0           ChildTable$ref$ParentTable
1              1           ParentTable$ref$GrandParentTable

You re-enable the constraint, using the CHECK argument on the ALTER TABLE statement:

--For not trusted, simply use CHECK:
ALTER TABLE Demo.ParentTable
    CHECK CONSTRAINT ParentTable$ref$GrandParentTable; 
GO 

--To enable and make trusted add WITH CHECK, leading to my FAVORITE bit of DDL syntax, WITH CHECK CHECK:
ALTER TABLE Demo.ParentTable
    WITH CHECK CHECK CONSTRAINT ParentTable$ref$GrandParentTable; 
GO 

But how to create it disabled? Answer, you can't, based on how SQL Server tools scripted the disabled constraint. I guessed the answer, but that didn't make it seem weirder to me. It is a two step process. First, create the constraint WITH NOCHECK, and then disable it:

--DROP the existing constraint on the Demo.ParentTable:
ALTER TABLE Demo.ParentTable
    DROP CONSTRAINT ParentTable$ref$GrandParentTable;

--The create the disabled constraint by adding the non-trusted version, the disabling it 
ALTER TABLE Demo.ParentTable
    WITH NOCHECK
        ADD CONSTRAINT ParentTable$ref$GrandParentTable
            FOREIGN KEY (GrandParentTableId) REFERENCES Demo.GrandParentTable(GrandParentTableId);

ALTER TABLE Demo.ParentTable
    NOCHECK CONSTRAINT ParentTable$ref$GrandParentTable;
GO 

Of course, you don't have to create it as non-trusted to disable it, but it is probably not the desired process to create a constraint, check all of the data, just to have it be turned off immediately.

Note: changed first sentence to be a bit more clear…

Published Sunday, May 07, 2017 9:56 PM by drsql
Filed under: ,

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Brenda said:

This is all good information. Thanks. But I really want the utility that drops the foreign key, truncates a table and then rebuilds the foreign key? I ran into this issue just recently and the utility would be great. I'm unable to register to receive updates to this post - New User Registration Disabled. Guess I will just keep checking back. Thanks again

May 13, 2017 10:09 AM
 

John said:

I'm curious as to why you would want to have a foreign key enabled, but not trusted? I came into a new environment several months ago and still finding things I don't see the logic for and this is one of them.

Hundreds of foreign keys enabled, but not trusted. Doesn't this cause an issue with the optimizer when using joins on this type of foreign key?

May 13, 2017 2:29 PM
 

drsql said:

@Brenda Sorry I didn't see your comment earlier. I hope you see the latest post in the series. It really works pretty well.

@John. You wouldn't really want them that way. But if you have a really large table, you may not be able to re-trust them all in some cases. It definitely will cause there to be less information about the columns for the optimizer. It may not be terrible, since there still will be the stats.

When I built the utility though, I wanted to honor whatever settings were already there, and give the power to set them to different states depending on what was there, and to change

July 11, 2017 4:41 PM

Leave a Comment

(required) 
(required) 
Submit

This Blog

Syndication

Links to my other sites

Archives

Privacy Statement