9

I have two tables, Sale and Product. Sale has a foreign key constraint referencing Product. The foreign key was created WITH NOCHECK and immediately disabled after creation. I want to enable and trust the foreign key constraint. Enabling it works but I can't get it to be trusted.

Similar questions on StackOverflow and various blogs indicate that running ALTER TABLE T WITH CHECK CHECK CONSTRAINT C should result in is_disabled=0 and is_not_trusted=0, but is_not_trusted is always 1 for me. What am I doing wrong?

I tried to put sample code on SQL Fiddle but it didn't like the "DBCC" commands, so here it is:

-- "_Scratch" is just a sandbox DB that I use for testing stuff.
USE _Scratch

CREATE TABLE dbo.Product
(
  ProductKeyId INT PRIMARY KEY NOT NULL,
  Description VARCHAR(40) NOT NULL
)

CREATE TABLE dbo.Sale
(
  ProductKeyId INT NOT NULL,
  SaleTime DATETIME NOT NULL,
  Value MONEY NOT NULL
)

ALTER TABLE dbo.Sale WITH NOCHECK
  ADD CONSTRAINT FK_Product_ProductKeyId FOREIGN KEY (ProductKeyId)
  REFERENCES dbo.Product (ProductKeyId) NOT FOR REPLICATION;

ALTER TABLE dbo.Sale NOCHECK CONSTRAINT FK_Product_ProductKeyId

INSERT INTO dbo.Product VALUES (1, 'Food')
INSERT INTO dbo.Sale VALUES (1, GETDATE(), 1.00)

-- Check the disabled/trusted state
SELECT name, is_disabled, is_not_trusted
FROM sys.foreign_keys
WHERE name = 'FK_Product_ProductKeyId'

  -- name                     is_disabled  is_not_trusted
  -- FK_Product_ProductKeyId  1            1

-- Check the FK_Product_ProductKeyId constraint
DBCC CHECKCONSTRAINTS('FK_Product_ProductKeyId')

  -- DBCC execution completed.
  -- If DBCC printed error messages, contact your system administrator.

-- Check all constraints on Sale table
DBCC CHECKCONSTRAINTS('Sale')

  -- DBCC execution completed.
  -- If DBCC printed error messages, contact your system administrator.

-- Add the constraint and check existing data
ALTER TABLE Sale WITH CHECK CHECK CONSTRAINT FK_Product_ProductKeyId

-- Check the disabled/trusted state
SELECT name, is_disabled, is_not_trusted
FROM sys.foreign_keys
WHERE name = 'FK_Product_ProductKeyId'

  -- name                     is_disabled  is_not_trusted
  -- FK_Product_ProductKeyId  0            1

-- Check the FK_Product_ProductKeyId constraint
DBCC CHECKCONSTRAINTS('FK_Product_ProductKeyId')

  -- DBCC execution completed.
  -- If DBCC printed error messages, contact your system administrator.

-- Check all constraints on Sale table
DBCC CHECKCONSTRAINTS('Sale')

  -- DBCC execution completed.
  -- If DBCC printed error messages, contact your system administrator.
WileCau
  • 2,057
  • 1
  • 24
  • 34

1 Answers1

18

Based on your examples, I have also tried:

  • Drop and recreate the foreign key.
  • Drop and recreate the table.

Then I noticed something in the command:

NOT FOR REPLICATION

It seems if a constraint is created with NOT FOR REPLICATION, it is always not trusted.

Quoting from Books Online:

In some cases, it is desirable for user activity in a replication topology to be treated differently from agent activity. For example, if a row is inserted by a user at the Publisher and that insert satisfies a check constraint on the table, it might not be required to enforce the same constraint when the row is inserted by a replication agent at the Subscriber. The NOT FOR REPLICATION option allows you to specify that the following database objects are treated differently when a replication agent performs an operation:

Foreign key constraints

The foreign key constraint is not enforced when a replication agent performs an insert, update, or delete operation.

It looks like the IS_NOT_TRUSTED setting is only relevant for replication influenced by IS_NOT_FOR_REPLICATION. I guess as long as the constraint is enforced on the server you are working on, it should be fine. So I went ahead and confirmed it:

SELECT name, is_disabled, is_not_trusted
FROM sys.foreign_keys
WHERE name = 'FK_Product_ProductKeyId'

name                    is_disabled is_not_trusted
FK_Product_ProductKeyId 0            1

INSERT INTO dbo.Sale VALUES (2, GETDATE(), 1.00)

Msg 547, Level 16, State 0, Line 1
The INSERT statement conflicted with the FOREIGN KEY constraint "FK_Product_ProductKeyId". The conflict occurred in database "_Scratch", table "dbo.Product", column 'ProductKeyId'.
The statement has been terminated.

If you still want to see IS_NOT_TRUSTED = 0 for peace of mind, just recreate the foreign key without NOT FOR REPLICATION.

In case if those of you are wondering, I have verified the same effect on CHECK constraints as well.

Ben Gribaudo
  • 5,057
  • 1
  • 40
  • 75
Louie Bao
  • 1,632
  • 2
  • 16
  • 23
  • Louie, thank you for taking the time to do the tests, that's a great answer, I wish I could give you more than one upvote :) I was trying to get the constraints trusted because I read somewhere that non-trusted ones can affect performance of some queries. I inherited the database and we don't replicate but everything seems to be "NOT FOR REPLICATION", which may be a case of "speculative generality". – WileCau May 01 '13 at 08:06
  • @WileCau You actually get an improvement in execution time when FKs are marked not trusted. I'm in no way encouraging this practice. However, it is very useful to moving large amounts of data, from say staging sql to production sql. check here for more info http://sqlblog.com/blogs/hugo_kornelis/archive/2007/03/29/can-you-trust-your-constraints.aspx – harsimranb Feb 11 '14 at 23:21
  • @Pathachiever11, you are right, the reason the constraints were disabled was because some tables were initially populated from another known consistent database so they just slowed down the data migration. After the initial migration the constraints should have been re-enabled to enforce consistency on new data, but they were forgotten. – WileCau Feb 12 '14 at 03:57
  • @WileCau I'm going through the same scenario in my environment. We used the BCP tool to migrate lots of data, and then realized that the FKs were marked untrusted. It is quite handy for large data migrations. – harsimranb Feb 12 '14 at 17:14