You can temporarily disable check constraints and database triggers in SQL Server using a small set of ALTER TABLE commands. This is a legitimate and common technique when performing bulk data loads, migrations, or targeted data corrections where constraint validation would either fail or create unacceptable performance overhead. Done correctly, it's safe and reversible. Done carelessly, it can leave your database in an inconsistent state.

This article explains exactly how to disable and re-enable check constraints and triggers across an entire database, when it's appropriate to do so, and what risks to watch for.

Why Would You Need to Disable Check Constraints or Triggers?

There are several real-world scenarios where temporarily disabling check constraints is the right call.

The most common is a bulk data load or restore. If you're loading a large dataset from an external source, or restoring data from a backup into a subset of tables, SQL Server will validate every row against every constraint as it's inserted. On tables with complex foreign key chains or multi-column check constraints, this can add significant overhead, sometimes turning a 10-minute load into a 90-minute one.

Data migrations between environments are another typical case. When moving data from a legacy system, the source data may have been valid under old business rules that no longer match your current constraints. You might need to load the data first, clean it up, then re-enable constraints once everything is consistent.

Trigger suppression is slightly different. Triggers fire on data modification events and often write to audit tables, send notifications, or cascade changes elsewhere. During a bulk correction or a scripted data fix, you generally don't want those side effects firing hundreds of thousands of times. Disabling triggers for the duration of the operation keeps things clean.

Neither of these operations should be taken lightly. The commands below give you significant power over your data integrity controls, and that means you need a solid plan before and after.

How to Disable Check Constraints on All Tables

The undocumented stored procedure sp_msforeachtable iterates over every user table in the current database and executes a command against each one. It's been available in SQL Server for many years and is widely used in operational scripts, though Microsoft hasn't formally documented it and it could theoretically be removed in a future version.

To disable all check constraints across every table in the database:

EXEC sp_msforeachtable "ALTER TABLE ? NOCHECK CONSTRAINT ALL"

The NOCHECK keyword tells SQL Server to stop validating the constraint for subsequent data modifications. Existing data is not re-evaluated. The constraint definition remains in place, it just isn't enforced.

How to Re-enable Check Constraints

Re-enabling constraints is slightly more involved than disabling them, and the syntax matters.

EXEC sp_msforeachtable "ALTER TABLE ? WITH CHECK CHECK CONSTRAINT ALL"

The WITH CHECK clause is critical here. It instructs SQL Server to validate all existing data in the table against the constraint before re-enabling it. Without WITH CHECK, SQL Server will mark the constraint as enabled but also mark it as "not trusted," meaning the query optimiser cannot rely on it for plan generation. A constraint marked as not trusted is essentially decorative from a query performance standpoint.

You can verify the trust status of your constraints after re-enabling them:

SELECT 
    t.name AS TableName,
    c.name AS ConstraintName,
    c.is_disabled,
    c.is_not_trusted
FROM sys.check_constraints c
JOIN sys.tables t ON c.parent_object_id = t.object_id
ORDER BY t.name, c.name;

After running the WITH CHECK CHECK CONSTRAINT ALL command, both is_disabled and is_not_trusted should be 0 for every constraint. If is_not_trusted is still 1, the re-enable failed validation on at least some rows, which means your data doesn't actually satisfy the constraint.

How to Disable Database Triggers

The same sp_msforeachtable approach works for triggers. This command disables all triggers on all user tables:

EXEC sp_MSforeachtable @command1 = "ALTER TABLE ? DISABLE TRIGGER ALL"

This is particularly useful when running data correction scripts that would otherwise fire audit triggers repeatedly, or when loading data that would cascade changes through trigger logic before you're ready for that to happen.

How to Re-enable Database Triggers

Once your data operation is complete, re-enable all triggers with:

EXEC sp_MSforeachtable @command1 = "ALTER TABLE ? ENABLE TRIGGER ALL"

Unlike constraints, there's no "trusted" state to worry about with triggers. Re-enabling them simply means they'll fire again on the next qualifying data modification event.

What Are the Risks of Disabling Constraints and Triggers?

This is where most articles stop short. The commands above are straightforward, but the operational risks deserve attention.

Data integrity violations. If your data load or correction introduces rows that violate a check constraint, re-enabling with WITH CHECK will fail. You'll need to identify and resolve the offending rows before constraints can be fully restored. Don't assume the data is clean just because it loaded without errors.

Orphaned records. Foreign key constraints prevent orphaned child records. If you disable them during a load and your source data contains referential integrity problems, you can end up with records that reference non-existent parent rows. This kind of corruption can be difficult to detect and painful to clean up.

Missed audit events. Disabling triggers means your audit trail has a gap. If your organisation has compliance requirements around data change logging, you need to account for this. Document what was changed, when, and why, and consider whether your compliance framework requires a manual audit entry for the period when triggers were disabled.

Concurrent activity. These commands affect the entire database, not just your session. If other users or applications are writing to the database while constraints are disabled, their changes won't be validated either. In a production environment, this is a serious concern. Ideally, you should be doing this work during a maintenance window with the database in restricted user mode.

A Safer Approach for Production Environments

Rather than disabling constraints and triggers database-wide, consider targeting only the specific tables you're working with. This limits the blast radius if something goes wrong.

For a single table:

-- Disable constraints on one table
ALTER TABLE dbo.YourTable NOCHECK CONSTRAINT ALL

-- Perform your data operation here

-- Re-enable with validation
ALTER TABLE dbo.YourTable WITH CHECK CHECK CONSTRAINT ALL

For triggers on a single table:

-- Disable triggers on one table
ALTER TABLE dbo.YourTable DISABLE TRIGGER ALL

-- Perform your data operation here

-- Re-enable triggers
ALTER TABLE dbo.YourTable ENABLE TRIGGER ALL

This approach is more controlled and reduces the risk of unintended side effects on tables you weren't planning to touch.

Key Takeaways

  • Use NOCHECK CONSTRAINT ALL to disable check constraints and WITH CHECK CHECK CONSTRAINT ALL to re-enable them. The WITH CHECK clause is not optional if you want the query optimiser to trust your constraints.
  • Always verify constraint trust status after re-enabling using sys.check_constraints. An is_not_trusted value of 1 means your data has integrity problems that need resolving.
  • Disabling triggers suppresses all side effects including audit logging. Document any maintenance windows where triggers were disabled, especially in regulated environments.
  • Prefer table-level commands over database-wide operations in production. Target only the tables you're working with to limit risk.
  • These operations should be performed during scheduled maintenance windows with reduced or no concurrent user activity.

If you're regularly performing data migrations, bulk loads, or environment refreshes, having a consistent, tested procedure for managing constraints and triggers is part of good database hygiene. At DBA Services, our managed support and database health check services include reviewing exactly this kind of operational practice, making sure your team has safe, documented procedures for maintenance tasks that touch data integrity controls. Get in touch if you'd like a second opinion on how your SQL Server environment handles these scenarios.