2

I needed to alter the conditions of existing constraint - so I dropped it and recreated with the new conditions.

This failed, because there were 800k+ rows, that didn't meet the condition. That strikes me as odd, because

SELECT * FROM sys.[check_constraints] cc

showed is_not_trusted as 0

Anyway, I recreated the constraint again, this time with hint WITH NOCHECK This time it passed and I expected is_not_trusted to be 1, but it is still trusted.

Can someone explain it to me?

My code:

ALTER TABLE dbo.MyTable   
DROP CONSTRAINT CK_MyTable;  
GO  

ALTER TABLE dbo.MyTable WITH NOCHECK
ADD CONSTRAINT CK_MyTable 
CHECK
(
    -- 7 conditions, mainly checking null and not null

)
1
  • Add DDL for the CREATE TABLE and check constraint expression to your question. – Dan Guzman Oct 16 '18 at 9:35
1

This might not be an accepted answer and I am ok with that but I cannot put the whole thing in a comment.

I tested your scenario with SQL Server 2016, SP2 and SQL Server 2017 CU9. In both cases I get expected result for is_not_trusted column value. Meaning 1 while using WITH NOCHECK and 0 while using `WITH CHECK'

Here is my test code. Some of the code I used is from Search Engine Q&A #9: How to update constraints? written by Paul Randal.

DROP DATABASE IF EXISTS checkConstraints;
CREATE DATABASE checkConstraints;
GO

USE [checkConstraints]
GO

DROP TABLE IF EXISTS dbo.Sales;
CREATE TABLE dbo.Sales (salesID INT IDENTITY, SalesDate DATETIME);
GO

CREATE CLUSTERED INDEX Sales_CL ON Sales (SalesID);
CREATE NONCLUSTERED INDEX Sales_NCL ON Sales (SalesDate);
GO

SET NOCOUNT ON;
GO

DECLARE @a INT;
SELECT @a = 1;
WHILE (@a < 1000)
BEGIN

INSERT INTO Sales VALUES (GETDATE ());
SELECT @a = @a + 1;

END;
GO

ALTER TABLE Sales DROP CONSTRAINT IF EXISTS  [CK_Sales_nocheck];
GO
ALTER TABLE Sales WITH NOCHECK ADD CONSTRAINT [CK_Sales_nocheck] CHECK (YEAR (SalesDate) >= 2005 AND YEAR (SalesDate) < 2008);
GO

ALTER TABLE Sales DROP CONSTRAINT IF EXISTS [CK_Sales_check];
GO
ALTER TABLE Sales WITH CHECK ADD CONSTRAINT [CK_Sales_check] CHECK (YEAR (SalesDate) >= 2005 AND YEAR (SalesDate) < 2019);
GO

SELECT name, is_not_trusted FROM sys.check_constraints;
GO

Result I get is following:

enter image description here

1
  • 1
    Thank you for the effort. This demonstrates exactly what I would expect. But I have no idea how to replicate mine (unexpected) scenario. – Zikato Oct 17 '18 at 6:51

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.