title: How to make foreign key constraints trusted?
body: <p>I have two tables, Sale and Product. Sale has a foreign key constraint referencing Product. The foreign key was created <code>WITH NOCHECK</code> 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.</p>
  
  <p>Similar questions on StackOverflow and various blogs indicate that running <code>ALTER TABLE T WITH CHECK CHECK CONSTRAINT C</code> should result in <code>is_disabled=0</code> and <code>is_not_trusted=0</code>, but <code>is_not_trusted</code> is always 1 for me. What am I doing wrong?</p>
  
  <p>I tried to put sample code on SQL Fiddle but it didn't like the "DBCC" commands, so here it is:</p>
  
  <pre><code>-- "_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.
  </code></pre>