Azure sql database -some FK check constraints automatically change to nocheck

Question

Azure sql database -some FK check constraints automatically change to nocheck

Puppala, Sreedhar 0

Azure sql database -some FK check constraints automatically change to nocheck , no alter was applied , checked audit log as well.anyone faced similiar issue

Erland Sommarskog 137.4K Reputation points MVP Volunteer Moderator

2026-09-09T20:56:53.82+00:00
No, that did not happen automatically. Someone ran some commands. The user may not have explicitly execute ALTER TABLE commands, but it may have happened implicitly because of some operation.

If you did not have proper auditing in place to capture DDL commands, it's difficult to find out what happened.

The easiest way to track such events is to set up an DDL trigger:

CREATE TRIGGER ddltri ON DATABASE FOR DDL_DATABASE_LEVEL_EVENTS AS INSERT ddlaudit(evendata, time, username) SELECT eventdata(), sysdatetime(), USER
Puppala, Sreedhar 0 Reputation points

2026-09-09T21:44:37.2366667+00:00

Hi Erland .we have audit setup on azure sql database at azure -server level but that doesnt show anything. created table ddlaudit and added trigger and hope it helps, Will update the status
Puppala, Sreedhar 0 Reputation points

2026-09-11T13:54:11.42+00:00

trigger is capturing normal DDL , however still doesnt capture the statements which are changing check to nocheck for some FKs . can SSIS or any bulk jobs cause this ?
Erland Sommarskog 137.4K Reputation points MVP Volunteer Moderator

2026-09-11T16:36:06.91+00:00

Interesting. Theoretically, an "evil" process could disable the DDL trigger (and that does not fire the DDL trigger), but it sounds a little unlikely.

Maybe you should describe more closely what you see. How to you conclude that the FKs are set to NOCHECK? Are you looking in sys.foreign_keys, or are you making some other observations? When you have observed this, what statements have you run to correct the situation?
Puppala, Sreedhar 0 Reputation points

2026-09-11T18:00:38.96+00:00

yes i check using SELECT schema_name(f.schema_id)||'.'||name,name FROM sys.foreign_keys AS f Where Is_Not_Trusted = 1;

and if i find issue then set the nocheck constraints manually to check . The alter to set to check is recorded by trigger /audit .

after doing this in day or two find some new nocheck constraints. does can SSIS or any bulk jobs cause this without getting logged for alter table to make it nocheck.

1 answer

Your answer

Erland Sommarskog 137.4K Reputation points MVP Volunteer Moderator

2026-09-09T20:56:53.82+00:00

No, that did not happen automatically. Someone ran some commands. The user may not have explicitly execute ALTER TABLE commands, but it may have happened implicitly because of some operation.

If you did not have proper auditing in place to capture DDL commands, it's difficult to find out what happened.

The easiest way to track such events is to set up an DDL trigger:

CREATE TRIGGER ddltri ON DATABASE FOR DDL_DATABASE_LEVEL_EVENTS AS INSERT ddlaudit(evendata, time, username) SELECT eventdata(), sysdatetime(), USER
Puppala, Sreedhar 0 Reputation points

2026-09-09T21:44:37.2366667+00:00

Hi Erland .we have audit setup on azure sql database at azure -server level but that doesnt show anything. created table ddlaudit and added trigger and hope it helps, Will update the status
Puppala, Sreedhar 0 Reputation points

2026-09-11T13:54:11.42+00:00

trigger is capturing normal DDL , however still doesnt capture the statements which are changing check to nocheck for some FKs . can SSIS or any bulk jobs cause this ?
Erland Sommarskog 137.4K Reputation points MVP Volunteer Moderator

2026-09-11T16:36:06.91+00:00

Interesting. Theoretically, an "evil" process could disable the DDL trigger (and that does not fire the DDL trigger), but it sounds a little unlikely.

Maybe you should describe more closely what you see. How to you conclude that the FKs are set to NOCHECK? Are you looking in sys.foreign_keys, or are you making some other observations? When you have observed this, what statements have you run to correct the situation?
Puppala, Sreedhar 0 Reputation points

2026-09-11T18:00:38.96+00:00

yes i check using SELECT schema_name(f.schema_id)||'.'||name,name FROM sys.foreign_keys AS f Where Is_Not_Trusted = 1;

and if i find issue then set the nocheck constraints manually to check . The alter to set to check is recorded by trigger /audit .

after doing this in day or two find some new nocheck constraints. does can SSIS or any bulk jobs cause this without getting logged for alter table to make it nocheck.

Answer 1

yes i check using SELECT schema_name(f.schema_id)||'.'||name,name FROM sys.foreign_keys AS f Where Is_Not_Trusted = 1;

Aha. Sorry, I should have realised that directly. Yes, that flag can be set to 1 without DDL. Here is one example how this can happen. We have this setup:

CREATE TABLE master (id int NOT NULL,
                     somedata varchar(23) NOT NULL,
                     CONSTRAINT pk_master PRIMARY KEY (id)
)
INSERT master(id, somedata)
   VALUES(1, 'somedata')
go
CREATE TABLE detail (id int NOT NULL,
                     detailno int NOT NULL,
                     moredata varchar(24) NOT NULL,
                     CONSTRAINT pk_details PRIMARY KEY (id, detailno),
                     CONSTRAINT fk_details_master FOREIGN KEY (id) REFERENCES master(id)
)
go
SELECT is_not_trusted FROM sys.foreign_keys WHERE object_id = object_id('fk_details_master')

The SELECT returns 0 at this point.

Then create a local file, slask.bcp, with this content:

1;1;moredata

Then run from the command line:

bcp detail in slask.bcp -c -t; -S yourserver.database.windows.net -U username -P pwd -d yourdb

Now run that SELECT again

SELECT is_not_trusted FROM sys.foreign_keys WHERE object_id = object_id('fk_details_master')

The value is now 1.

This is because BCP, by default, does not check constraints. This makes it go faster. It also permits you to load data in parallel without considering dependency order. But since data is loaded without checking constraints, the constraints can no longer be trusted, but you need to run ALTER tbl WITH CHECK CHECK to make the constraint trusted again.

Drop the tables and re-recreate them as above. Run the BCP this way this time:

bcp detail in slask.bcp -c -t; -S yourserver.database.windows.net -U username -P pwd -d yourdb -hCHECK_CONSTRAINTS

This time the SELECT still returns 0.There are other ways you can do bulk inserts with the same effect. For instance OPENROWSET(BULK). You mentioned SSIS. I don't use SSIS and I know very little about it, but I would expect it to be able to use bulk-load methods that bypasses constraints by default, because, again, that loads data faster. I would expect the same be true for Azure Data Factory.