What is causing my constraint to be untrusted?

Tom
Helping out
5 minutes
Page content

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.

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

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

(Should be run in the context of 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

CHECK_CONSTRAINTS

Specifies that all constraints on the target table or view must be checked during the bulk-import operation. Without the CHECK_CONSTRAINTS hint, any CHECK, and FOREIGN KEY constraints are ignored, and after the operation the constraint on the table is marked as not-trusted

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.
They both return an empty result set, meaning 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.

Queries that return untrusted constraints output shows both tables

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.

Queries that return untrusted constraints output shows both tables

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.

Queries that return untrusted constraints output shows both tables

Tracking the culprit 🔗︎

Let’s get back to the XE output.

All the collected events

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 leverage the tsql_stack and query_hash_signed columns as I blogged here:

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