is_not_trusted in SQL Server

How to know your all rows are validated against check Constrains?

    Suppose you have a table with huge records and in column EmailID has CHECK constraints, how you can make you sure that all data are validated through the check constraints. There is chance where a developer has made the check constraint disable and loaded the data then he has made the constraint enable. So that your table has invalid data even though you have check constraint on the table.

 Here quick way to look it sys.check_constraints table where we have column called is_not_trusted if it is 0 then you are in safe and all data are validated against CHECK constraints. If 1 then your data have issue.

 Let’s run through below test case:

-- drop table dbo.MasterTable

CREATE TABLE dbo.MasterTable (

      id INT

      ,NAME VARCHAR(100) CHECK (

            NAME IN (

                  'vikas'

                  ,'amit'

                  )

            )

      )

GO

INSERT INTO MasterTable

VALUES (

      12

      ,'vikas'

      )

/* This will throw the error as data is conflict with check constraints*/

INSERT INTO MasterTable

VALUES (

      18

      ,'invalid test data'

      ) – This will through error GO 100

/* Disable the constraints */

ALTER TABLE dbo.MasterTable NOCHECK CONSTRAINT CK__MasterTabl__name__6B9AB725

GO

/* Now load the invalid data */

INSERT INTO MasterTable

VALUES (

      18

      ,'invalid test data'

      ) – This will through error GO 100

/* Enable the constraints */

ALTER TABLE dbo.MasterTable CHECK CONSTRAINT CK__MasterTabl__name__6B9AB725

If you look the table you have junk data that’s breaking the CHECK constraints.

Now if you see the Meta data.

/* Have the list of the check constraints*/

     

SELECT is_not_trusted

      ,*

FROM sys.check_constraints

You will find the values 1 for is_not_trusted column.

 While enabling if you want to check all the existing data against CHECK constraints then go with WITH CHECK

ALTER TABLE dbo.MasterTable

      WITH CHECK CHECK CONSTRAINT CK__MasterTabl__name__6B9AB725

n   Using below query we can validate all the constraints against data.

 DBCC CHECKCONSTRAINTS  ('dbo.MasterTable ' ) with ALL_CONSTRAINTS