Published on

December 30, 2014

Understanding the is_not_trusted Column in sys.foreign_keys

As a SQL Server database administrator, I often receive questions from readers that make me think and explore new concepts. One such question I recently received via email was about the meaning of the “is_not_trusted” column in the sys.foreign_keys table. The documentation in books online was not very clear, so I decided to dive deeper into this topic and share my findings with you.

Before we delve into the details, let’s first understand the purpose of the sys.foreign_keys table. This table stores metadata about foreign key constraints in a database. It provides information about the relationships between tables and helps enforce referential integrity.

Now, let’s consider an example to better understand the is_not_trusted column. Imagine we have three tables: EmpMaster, AddressMaster, and EmpAddress. The EmpAddress table serves as a mapping table between the EmpMaster and AddressMaster tables. To enforce referential integrity, we create foreign key constraints between these tables.

CREATE TABLE EmpMaster (
    Emp_id INT PRIMARY KEY,
    FName VARCHAR(10),
    LName VARCHAR(10)
)

CREATE TABLE AddressMaster (
    AddressId INT PRIMARY KEY,
    Address VARCHAR(100),
    Status CHAR(1)
)

CREATE TABLE EmpAddress (
    Emp_id INT,
    AddressId INT
)

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

Now, let’s insert some data into the master tables:

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

If we try to insert a value (1, 2) into the EmpAddress table, we will get an error because the value 2 is not available in the parent table AddressMaster:

INSERT INTO EmpAddress (Emp_id, AddressId) VALUES (1, 2)
-- Error: The INSERT statement conflicted with the FOREIGN KEY constraint "FK_EmpLocation_AddressMaster". The conflict occurred in database "SQLAuthDB", table "dbo.AddressMaster", column 'AddressId'.

Now, let’s look at the metadata for the foreign key in the sys.foreign_keys catalog view:

SELECT name, is_not_trusted FROM sys.foreign_keys

The is_not_trusted column is set to zero by default, indicating that the child table (EmpAddress) does not have any data that is not present in the parent table (AddressMaster).

However, we can disable the foreign key constraint using the following command:

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

After disabling the constraint, we can insert the inconsistent value (1, 2) into the EmpAddress table:

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

Now, if we query the sys.foreign_keys view again, we will see that the is_not_trusted column has a value of 1, indicating that the system has not verified the consistency of the data.

To enable the constraint again, we can use the following command:

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

It’s worth noting that the “CHECK” keyword is used twice in the syntax to enable the constraint. After modifying the inconsistent value in the EmpAddress table, we can re-execute the ALTER TABLE command, and it should work without any conflicts.

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

USE MASTER;
DROP DATABASE SQLAuthDB;

I hope this article has provided you with a clear understanding of the is_not_trusted column in the sys.foreign_keys table and how it functions. If you have any further questions or have used this feature in your own projects, feel free to let me know.

Click to rate this post!
[Total: 0 Average: 0]

Let's work together

Send us a message or book free introductory meeting with us using button below.