Como as restrições de chave estrangeira podem ser temporariamente desativadas usando o T-SQL?

Se você quiser desativar todas as restrições da base de dados, basta executar este código:

-- disable all constraints
EXEC sp_MSforeachtable "ALTER TABLE ? NOCHECK CONSTRAINT all"

Para os voltar a ligar, corre: (a impressão é opcional, claro, e é apenas uma listagem das tabelas)

-- enable all constraints
exec sp_MSforeachtable @command1="print '?'", @command2="ALTER TABLE ? WITH CHECK CHECK CONSTRAINT all"

Acho-o útil quando povoamos dados de uma base de dados para outra. É uma abordagem muito melhor do que deixar cair as restrições. Como você mencionou, é muito útil quando se deixa cair todos os dados na base de dados e se repovoam os dados (digamos em ambiente de teste).

Se você estiver apagando todos os dados, você pode achar esta solução útil.

Também às vezes é útil desativar todos os gatilhos também, você pode ver a solução completa aqui.

http://www.sqljunkies.com/WebLog/roman/archive/2005/01/30/7037.aspx

-- Disable all table constraints

ALTER TABLE MyTable NOCHECK CONSTRAINT ALL

-- Enable all table constraints

ALTER TABLE MyTable WITH CHECK CHECK CONSTRAINT ALL

-- Disable single constraint

ALTER TABLE MyTable NOCHECK CONSTRAINT MyConstraint

-- Enable single constraint

ALTER TABLE MyTable WITH CHECK CHECK CONSTRAINT MyConstraint

O padrão SQL-92 permite que um conteúdo seja declarado como DEFERENTE para que possa ser diferido (implícita ou explicitamente) dentro do escopo de uma transação. Infelizmente, o SQL Server ainda não tem esta funcionalidade SQL-92.

Para mim, mudar uma restrição para o NOCHECK é como mudar a estrutura do banco de dados em tempo real -- certamente que a queda de restrições é -- e algo a ser evitado (por exemplo, os usuários requerem privilégios maiores).