CODEWITHSUNDEEP
sql-serverforeign-keys
Jero Franzani
Jero Franzani

Reputation: 473

How to know if a Constraint is actually with CHECK in SQL Server

I have to check a Constraint in case it was created with NOCHECK; the thing is that some of the constrained data has been fixed and checked after, so I need to check each constraint only if the check is still disabled. For example I have this code;

CODE1 (OK):

ALTER TABLE [dbo].[TABLE1]  WITH NOCHECK ADD  CONSTRAINT [FK_MYFK] FOREIGN KEY([TABLE1_id], [TABLE1_Codigo])
REFERENCES [dbo].[TABLE2] ([TABLE2_id], [TABLE2_Codigo])
GO
ALTER TABLE [dbo].[TABLE1] CHECK CONSTRAINT [FK_MYFK]
GO

Here the Check was added after;

CODE 2 (With the NOCHECK Bug i need to fix):

ALTER TABLE [dbo].[TABLE1]  WITH NOCHECK ADD  CONSTRAINT [FK_MYFK] FOREIGN KEY([TABLE1_id], [TABLE1_Codigo])
REFERENCES [dbo].[TABLE2] ([TABLE2_id], [TABLE2_Codigo])
GO
ALTER TABLE [dbo].[TABLE1] NOCHECK CONSTRAINT [FK_MYFK]
GO

So I need to know if the constraint has the CHECK

I Have Tried this but as it was created with the NOCHECK it always returns that my contraint is not trusted.

SELECT name, tbl = object_name(parent_obj)
FROM sysobjects
WHERE objectproperty(id, 'CnstIsNotTrusted') = 1

Is there a way (that does not include to drop and create the constraint again) to know if a constraint is actually with CHECK despite that it was created with NOCHECK?

Upvotes: 1

Views: 2955

Answers (3)

Lukasz Szozda
Lukasz Szozda

Reputation: 175576

Just query sys.check_constraints

SELECT *
FROM sys.check_constraints
WHERE is_disabled = 0;

Similiar sys.foreign_keys

is_disabled bit CHECK constraint is disabled.

is_not_trusted bit CHECK constraint has not been verified by the system for all rows.

To check if there are data that violate constraints use:

DBCC CHECKCONSTRAINTS WITH ALL_CONSTRAINTS

You have to tell SQL Server to not just enable the constraint, but to recheck all of the data that’s been loaded.

ALTER TABLE MyTableName WITH CHECK CHECK CONSTRAINT MyConstraintName;

Warning: For large tables you may need outage window

Upvotes: 1

Damien_The_Unbeliever
Damien_The_Unbeliever

Reputation: 239636

ALTER TABLE ... CHECK CONSTRAINT ... doesn't validate existing data - so it is still not trusted.

You need to do ALTER TABLE ... WITH CHECK CHECK CONSTRAINT ... (yes, CHECK twice) so that it both validates existing data and enforces it for new insertions

See ALTER TABLE:

The query optimizer does not consider constraints that are defined WITH NOCHECK. Such constraints are ignored until they are re-enabled by using ALTER TABLE table WITH CHECK CHECK CONSTRAINT ALL.

Upvotes: 3

JodyT
JodyT

Reputation: 4412

This should return all check constraints that are not trusted.

SELECT QUOTENAME(s.NAME) + '.' + QUOTENAME(OBJECT_NAME(i.parent_object_id)) + '.' + QUOTENAME(i.NAME) AS [Constraint]
FROM sys.check_constraints i
INNER JOIN sys.objects o ON i.parent_object_id = o.object_id
INNER JOIN sys.schemas s ON s.schema_id = o.schema_id
WHERE i.is_not_trusted = 1

If you want to make the constraints trusted again you can do that using the following query:

ALTER TABLE [dbo].[TABLE1] WITH CHECK CHECK CONSTRAINT [FK_MYFK]

The same can be done with foreign keys. Replace sys.check_constraints with sys.foreign_keys.

Upvotes: 2

Related Questions