What is causing my constraint to be untrusted?

The problem

In this scenario, you have discovered that one of your Check constraints or Foreign keys is not trusted.

Maybe you've detected it with a sp_Blitz, dbachecks or out of curiosity, with ad-hoc queries like these.

/* Check constraints and their trust state */
SELECT
    cc.object_id AS ccId
    , OBJECT_NAME(cc.parent_object_id) AS tableName
    , cc.name AS ccName
    , cc.is_not_trusted
FROM sys.check_constraints AS cc
WHERE
    cc.is_disabled = 0
    -- AND cc.is_not_trusted = 1

/* Foreign keys and their trust state */
SELECT
    fk.object_id AS fkId
    , OBJECT_NAME(fk.parent_object_id) AS tableName
    , fk.name AS fkName
    , fk.is_not_trusted
FROM sys.foreign_keys AS fk
WHERE
    fk.is_disabled = 0
    --AND fk.is_not_trusted = 1

Run these in the context of the target database.

We want to fix those untrusted constraints because the optimizer won't use them when coming up with a query plan.

Repeating offender

Okay, so you went through the effort of fixing them, but the next day your constraints are not trusted again. What gives?

If you are sure none of the DBAs or developers is doing this to spite you, the most common culprit is a BULK INSERT or bulk copy tool (bcp).

One of its parameters is -h (hints)
and one of those hints is CHECK_CONSTRAINTS

If you skip this parameter, bcp will mark the constraints as untrusted.

And the bulk operation can be run from several different sources.

• TSQL (BULK INSERT)
• bcp command line
• .NET application
• SSIS

No wonder you might have trouble finding the culprit.

Extended Events to the rescue

We'll set up an Extended Events (XE) session with three events to track each use case

• object_altered - if someone really is running the ALTERs against the constraint
• databases_bulk_copy_rows
• databases_bulk_insert_rows

For the bcp and bulk insert, respectively.
We'll grab additional audit global fields to locate the responsible process/person more quickly.

Demo

First, the XE definition.

CREATE EVENT SESSION TrackConstraintTrust
ON SERVER
ADD EVENT sqlserver.databases_bulk_copy_rows
(ACTION
    (
        sqlserver.client_app_name
        , sqlserver.client_hostname
        , sqlserver.database_name
        , sqlserver.query_hash_signed
        , sqlserver.server_instance_name
        , sqlserver.server_principal_name
        , sqlserver.sql_text
        , sqlserver.tsql_stack
    )
)
, ADD EVENT sqlserver.databases_bulk_insert_rows
(ACTION
    (
        sqlserver.client_app_name
        , sqlserver.client_hostname
        , sqlserver.database_name
        , sqlserver.query_hash_signed
        , sqlserver.server_instance_name
        , sqlserver.server_principal_name
        , sqlserver.sql_text
        , sqlserver.tsql_stack
    )
)
, ADD EVENT sqlserver.object_altered
(ACTION
    (
        sqlserver.client_app_name
        , sqlserver.client_hostname
        , sqlserver.database_name
        , sqlserver.server_instance_name
        , sqlserver.server_principal_name
        , sqlserver.sql_text
        , sqlserver.tsql_stack
    )
WHERE ddl_phase = 'Commit'
)

For the demo, Watch Live Data will do. But in your environment, you want to add an event_file target.
Start the event session and open the Live Data window. We'll get to the output later.

Now the environment - table and a child table with a Foreign key and Check constraint.

CREATE DATABASE InConstraintWeTrust
GO
USE InConstraintWeTrust
GO
CREATE TABLE dbo.[Order]
(
    Id int IDENTITY (1,1) NOT NULL
    , DateCreated datetime2(3) NOT NULL
        CONSTRAINT DF_Order_DateCreated DEFAULT SYSDATETIME()
    , CONSTRAINT PK_Order PRIMARY KEY CLUSTERED (Id)
)
GO
CREATE TABLE dbo.OrderItem
(
    Id int IDENTITY (1,1) NOT NULL
    , OrderId int NOT NULL
    , ProductName varchar(50) NOT NULL
    , Qty int NOT NULL
    , CONSTRAINT PK_OrderItem PRIMARY KEY CLUSTERED (Id)
    , CONSTRAINT FK_OrderItem_Order_Parent FOREIGN KEY (OrderId) REFERENCES dbo.[Order]
    , CONSTRAINT CK_OrderItem_PositiveQty CHECK (Qty > 0)
)
GO
INSERT INTO dbo.[Order] DEFAULT VALUES
GO 10

INSERT INTO dbo.OrderItem (OrderId, ProductName, Qty)
VALUES
    (1, 'abc', '2')
  , (1, 'bcd', '4')
  , (2, 'abc', '1')
  , (3, 'bcd', '2')

We can check the trustworthiness state with the two queries from earlier.
Both constraints come back with is_not_trusted = 0, so they are trusted.

ALTER TABLE

Let's test this scenario.

/* Disable FK constraint */
ALTER TABLE dbo.OrderItem NOCHECK CONSTRAINT FK_OrderItem_Order_Parent
/* Do something and enable constraint */
ALTER TABLE dbo.OrderItem CHECK CONSTRAINT FK_OrderItem_Order_Parent
GO
/* Disable CK constraint */
ALTER TABLE dbo.OrderItem NOCHECK CONSTRAINT CK_OrderItem_PositiveQty
/* Do something and enable constraint */
ALTER TABLE dbo.OrderItem CHECK CONSTRAINT CK_OrderItem_PositiveQty

After reenabling the constraints, they are not trusted.

We can make them trusted again with this code. Notice the WITH CHECK.

ALTER TABLE dbo.OrderItem WITH CHECK CHECK CONSTRAINT FK_OrderItem_Order_Parent
ALTER TABLE dbo.OrderItem WITH CHECK CHECK CONSTRAINT CK_OrderItem_PositiveQty

BULK INSERT

For the other two tests, I have created a simple .csv file and saved it at this path
D:\OrderItems.csv

,4,abcd,42
,4,asdasd,42
,5,asdasdasd,42
,5,dhdrh,42
,5,dasd,42
,6,fdhg,42

I'll run the BULK INSERT statement from SSMS. The CHECK CONSTRAINTS hint is commented out on purpose.

BULK INSERT dbo.OrderItem
FROM 'D:\OrderItems.csv'
WITH
(
    FIRSTROW = 1,
    FIELDTERMINATOR = ',',  --CSV field delimiter
    ROWTERMINATOR = '\n',   --Use to shift the control to next row
    TABLOCK
    --, CHECK_CONSTRAINTS
)

Let's check the trusted status again.

- Is the result the same?
- Yes.
- Did I reuse the same image?
- Also, yes. But who cares?

Reenable the constraints WITH CHECK again for the final test.

bcp Command line

This time I'll run the utility from the Windows Terminal. Change the parameters accordingly to your DEV environment.

bcp dbo.OrderItem in "D:\OrderItems.csv" -S localhost -d InConstraintWeTrust -T -c -t ','

And let's check the trusted status for the one last time.

Tracking the culprit

Let's get back to the XE output.

We can see all the events were successfully captured. The green highlighted events are when I restore the trusted status.

To track the source, we can use the client_app_name and client_hostname to narrow down the suspect.
If the process is nested somewhere, we can use the tsql_stack and query_hash_signed columns as I blogged here:

• Investigating Errors With Extended Events
• Query Hash and Query Plan Hash Mapping

I hope this will help you find the runaway process and explain the situation.

Thank you for reading