Does my table hold invalid data after importing data? Constraints have been violated?

It is rare to see a table without at least one constraint because constraints are required to manage data integrity rules. Although we add constraints, for certain scenario, we disable constraints as a temporary measure for increasing the performance of data importing. Keeping constraints enabled makes sure that no invalid records are inserted to the table but this slows down the entire loading process as each and every value has to be validated against the constraints. Because of this, rather than checking each value during the importing process, we disable constraints, complete the importing process and then enable constraints either checking all values loaded against the constraints or checking none of the values loaded against the constraints. Enabling without checking loaded values improves the overall performance but this can result the table holding invalid data, hence this should be done only when data can be trusted. 

However, if data has been loaded by disabling constraints and later constraints have been enabled without checking loaded values, and you need know whether constraints have been enabled without checking loaded values, specifically on foreign key and check constraints, how do you do it?

It can be done using two catalog views: sys.check_constraints and sys.foreign_keys. Both return a column called is_not_trusted and 1 indicates that rows have been inserted or updated without getting values validated against constraints. Although this indicator does not say that table hold invalid data, it gives an indication on possibilities of having invalid data, hence it is always better to have this checking as a part of routine checkup. And do not forget, "not trusted" constraints will lead to poorly-optimized query execution plans for queries running against tables with "not trusted" constraints.

Here is a sample code that shows the way of checking CHECK constraints with sys.check_constraint catalog view.

-- Create a table with a check constraint
USE tempdb;
GO

CREATE TABLE dbo.Customer
(
 CustomerId int not null
 , CustomerName varchar(10) not null
 , CreditAmount decimal(18,2) not null
 , CONSTRAINT Customer_SalaryCheck CHECK (CreditAmount < 100000)
);
GO

-- Inserting valid data
INSERT INTO dbo.Customer
 VALUES (1, 'Joe Brown', 65000);
INSERT INTO dbo.Customer
 VALUES (2, 'Mary Smith', 75000);


-- Inserting invalid data
-- This will not be possible as it violates the constraint
INSERT INTO dbo.Customer
 VALUES (3, 'Joe Brown', 110000);


-- Disabling the constraint and inserting the same again
-- Now it allows to insert a row with an invalid value
ALTER TABLE dbo.Customer NOCHECK CONSTRAINT Customer_SalaryCheck;
GO

INSERT INTO dbo.Customer
 VALUES (3, 'Joe Brown', 110000);
GO


-- Enabling the constraint.
-- Note that updated records are not validated
ALTER TABLE dbo.Customer CHECK CONSTRAINT Customer_SalaryCheck;
GO
-- Now table contains invalid data.

-- Checking with sys.check_constraints catalog view.
SELECT name, is_not_trusted FROM sys.check_constraints
WHERE parent_object_id  = OBJECT_ID('dbo.Customer');
-- This shows the value of is_not_trusted as 1
-- indicating that table might contain invalid data

-- Query the table and see. This shows 3 records including the invalid row.
SELECT * FROM dbo.Customer;
GO

-- If you want to make sure no invalid data exist
-- and validate all records agains the constraint
-- disable it again and enable it with CHECK option
ALTER TABLE dbo.Customer NOCHECK CONSTRAINT Customer_SalaryCheck;
GO

ALTER TABLE dbo.Customer WITH CHECK CHECK CONSTRAINT Customer_SalaryCheck;
GO
-- This will throw and error because current rowset has an invalid value.
-- This can be corrected by either updating it or deleting it.
DELETE FROM dbo.Customer WHERE CustomerId = 3;

-- Now constraint can be enabled with CHECK option
ALTER TABLE dbo.Customer WITH CHECK CHECK CONSTRAINT Customer_SalaryCheck;
GO
-- And if you run sys.check_constraint again, you will see 0 for is_not_trusted column.