SQL SERVER – What is is_not_trusted in sys.foreign_keys?

My greatest source of learning is via my blog. Many of you take time in asking your toughest questions and sometimes basic questions that make me think. This interaction creates a source of exploration for me and ultimately translates into my knowledge sharing. All questions asked by DBAs are not straight forward and sometimes I also have to do some research before providing answer to them. Here is another interesting question that I received via email recently –

Hi Pinal, What is the meaning of is_not_trusted column in sys.foreign_keys?

I would have told to check books online but if we look at books online for sys.foreign_keys, the column says “FOREIGN KEY constraint has not been verified by the system.” This is not very clear and easy to understand. No wonder he asked me this questions. Let us learn this using an example:

For simplicity let us assume we have three tables: EmpMaster, AddressMaster and EmpAddress using the below schema:

CREATE DATABASE SQLAuthDB
GO
USE SQLAuthDB
GO
CREATE TABLE EmpMaster(Emp_id INT PRIMARY KEY, FName VARCHAR(10), LName VARCHAR(10))
GO
CREATE TABLE AddressMaster(AddressId INT PRIMARY KEY, Address VARCHAR(100), Status CHAR(1))
GO
CREATE TABLE EmpAddress(Emp_id INT, AddressId INT)
GO

From the name you might have guessed that EmpAddress is the mapping table which can have only those values which are in master tables. So, to enforce referential integrity we should create foreign keys to avoid violation of parent-child relationship.

ALTER TABLE dbo.EmpAddress ADD CONSTRAINT
FK_EmpLocation_EmpMaster FOREIGN KEY
(
Emp_id
) REFERENCES dbo.EmpMaster
GO
ALTER TABLE dbo.EmpAddress ADD CONSTRAINT
FK_EmpLocation_AddressMaster FOREIGN KEY
(
AddressId
) REFERENCES dbo.AddressMaster
GO

We have set the basic structure to work. Now, let us insert one row in each master table as below.

INSERT INTO EmpMaster VALUES (1, 'Pinal','Dave')
GO
INSERT INTO AddressMaster VALUES (1, 'Bangalore, India', 'A')
GO

Now, if we try to insert a value (1, 2) in child table, we would get error

INSERT INTO EmpAddress (Emp_id, AddressId) VALUES (1, 2);

Msg 547, Level 16, State 0, Line 33

The INSERT statement conflicted with the FOREIGN KEY constraint “FK_EmpLocation_AddressMaster”. The conflict occurred in database “SQLAuthDB”, table “dbo.AddressMaster”, column ‘AddressId’.

The statement has been terminated.

This error is expected because “2” is not a valid value for AddressId because it’s not available in parent table i.e. AddressMaster.

Let’s look at metadata for foreign key in the catalog view sys.foreign_keys

SELECT name, is_not_trusted FROM sys.foreign_keys

SQL SERVER - What is is_not_trusted in sys.foreign_keys? trusted-1 

Is_not_trusted is set to zero by default which means that child is not having data which is not in parent. Now, let’s use below to disable the constraint.

ALTER TABLE [dbo].[EmpAddress] NOCHECK CONSTRAINT [FK_EmpLocation_AddressMaster]

And now insert the value.

INSERT INTO EmpAddress (Emp_id,AddressId) VALUES (1, 2)

This means, now we have inconsistent value in the child table. Executing the query on sys.foreign_keys again will confirm this assumption:

SELECT name, is_not_trusted FROM sys.foreign_keys

SQL SERVER - What is is_not_trusted in sys.foreign_keys? trusted-2 

As highlighted above, the value 1 in the column means that system has not verified the consistency of the data. Now, let’s try to enable the constraint again.

ALTER TABLE [dbo].[EmpAddress] WITH CHECK CHECK CONSTRAINT [FK_EmpLocation_AddressMaster] GO

You might say that above looks incorrect but “Check” twice is intended. That’s the correct syntax to enable the constraint. Here is the error one would receive while enabling it.

Msg 547, Level 16, State 0, Line 59

The ALTER TABLE statement conflicted with the FOREIGN KEY constraint “FK_EmpLocation_AddressMaster”. The conflict occurred in database “SQLAuthDB”, table “dbo.AddressMaster”, column ‘AddressId’.

Modify the value as below.

UPDATE EmpAddress
SET AddressId = 1
WHERE AddressId = 2

And now we can re-execute the ALTER TABLE command and it should work.

SQL SERVER - What is is_not_trusted in sys.foreign_keys? trusted-3

Once you are done with the above script, you can clean up the database by executing following script:

USE MASTER;
DROP DATABASE SQLAuthDB;

So this was a quick overview of the is_not_trusted value in the foreign_keys table and how it functions. Do let me know if you have every used this feature anywhere.

Reference: Pinal Dave (https://blog.sqlauthority.com)

,
Previous Post
SQL SERVER – Iterating over Metadata with BimlScript – Notes from the Field #061
Next Post
SQL SERVER – What is the query used in sp_cursorfetch and FETCH API_CURSOR?

Related Posts

22 Comments. Leave new

  • superrb article

    Reply
  • nakulvachhrajani
    January 9, 2015 9:36 pm

    Not having constraints that the SQL Server can trust may have an impact on query plan generation and hence performance of the system.

    We therefore ensure during every upgrade (and recommend to do so at periodic intervals) that all the constraints in our product database are always trusted.

    (Just to add, if we are using replication, SQL Server forces the constraints to be marked as NOT trusted because replication brings in data from external systems).

    Reply
  • Szymon Stawiński
    January 10, 2015 2:19 am

    You will also experience not trusted keys when using bulk inserts or bcp, which default behaviour is to ignore constraints

    Reply
  • A couple of additional points to watch out for:
    1) Re-enabling the index with NOCHECK would succeed (since existing values are not checked) but the constraint would remain untrusted
    2) Un-ticking the “check constraints” tickbox on the OLE DB Destination component in SSIS (which might seem sensible from a performance perspective – after all, you’ve just looked up all the foreign key values, right?) also leaves constraints untrusted

    Reply
  • Found this article useful. We suspect that untrusted foreign keys are not picked up by SQL optamiser when recompiling execution plan and wanted to get rid of them. Would you agree with this theory?

    Reply
  • The value of is_not_trusted column will become 1 as soon as we disable the constraint using NOCHECK. It’s not necessary that the table must have inconsistent data to set this column value as 1.

    Reply
  • The value of is_not_trusted column is still 1 , after modified the foreign key as CHECK Constraint. What it means?

    Reply
  • Hi Pinal,

    In our project we came across with some untrusted keys. After an analysis I found that some Bulk Insert is making the foreign keys untrusted. So on curosity I further looked if the refrential integrity is violated, but to my surprise it is not. The keys are untrusted but I am not able to insert any unwanted data and it is showing the expected message.

    Is it expected?

    Thank you.

    Regards,
    Nivedita

    Reply
  • Thanks Pinal. As always, you rock.

    We were struggling with our server to find out what is causing the flag to flip to 0 everyday after we rebuild them and its set to 0.
    The problem was un-ticking the “check constraints” tickbox on the OLE DB Destination component in SSIS as pointed by Nigel Harris.

    Reply
  • Unexpected Shutdown System or SQL Service. is it possible to is_not_trusted=1 …???

    Reply
  • What did you mean by that?

    Reply
  • It seems to me that you put the wrong query after your comment “And now we can re-execute the ALTER TABLE command and it should work.” Didn’t you intend to put the query with “CHECK CHECK” instead of the one with “NOCHECK” ?

    Reply
  • If you create the constraint with ‘NOT FOR REPLICATION’, then not_trusted appears to be always true, even if you run a check E.G.

    ALTER TABLE dbo.EmpAddress ADD CONSTRAINT
    FK_EmpLocation_EmpMaster FOREIGN KEY
    (
    Emp_id
    ) REFERENCES dbo.EmpMaster NOT FOR REPLICATION
    GO

    ALTER TABLE [dbo].[EmpAddress] WITH CHECK CHECK CONSTRAINT FK_EmpLocation_EmpMaster GO

    SELECT name, is_not_trusted, [is_not_for_replication] FROM sys.foreign_keys

    Output:
    FK_EmpLocation_EmpMaster 1 1

    I think this makes sense if the table is a subscriber because you are explicitly saying that the data should not be checked. However, this also makes me think that because is_not_trusted = 0, then the Foreign Key is not used to the benefit of query plans.

    What do you think Pinal?

    Reply

Leave a Reply