CODEWITHSUNDEEP
sql-serversql-server-2008
Justin808
Justin808

Reputation: 21512

What would disable a foreign key constraints?

I have a database and all the foreign key constraints are disabled. No one went in as disabled them manually. Can SQL Server disable them automatically for any reason? Would a backup or restore turn them off?

How do I go about turning them back on?

We're using SQL Server 2008.

Upvotes: 2

Views: 397

Answers (1)

SQLGuru
SQLGuru

Reputation: 108

SQL Server do not disable constraints automatically. Even Backup and restore can not turn them off on its own.

Only way to disable constraint is by running Alter Table command like this one:

ALTER TABLE table_Name NOCHECK CONSTRAINT all

To turn on all constraint on one table: 

ALTER TABLE table_Name CHECK CONSTRAINT all

To turn constrains for all table in database, you can use this command:

EXEC sp_MSforeachtable @command1="ALTER TABLE ? CHECK CONSTRAINT ALL" 
GO

Upvotes: 1

Related Questions