foreign - Desactiva las restricciones temporalmente(MS SQL)

disable all foreign key sql server

Question

Estoy buscando una manera de desactivar temporalmente todas las restricciones de DB (por ejemplo, relaciones de tablas).

Necesito copiar (usando INSERT) las tablas de un DB a otro DB. Sé que puedo lograrlo ejecutando comandos en el orden correcto (para no romper relaciones).

Pero sería más fácil si pudiera desactivar temporalmente las restricciones de comprobación y volver a encenderlo después del final de la operación.

es posible?

Answer 1

En realidad, puede deshabilitar todas las restricciones de la base de datos en un solo comando SQL y volver a habilitarlas llamando a otro comando único. Ver:

• ¿Pueden las restricciones de clave externa ser temporalmente desactivadas usando TSQL?

Actualmente estoy trabajando con SQL Server 2005 pero estoy casi seguro de que este enfoque también funcionó con SQL 2000

Answer 2

Puede desactivar las restricciones FK y CHECK solo en SQL 2005+ . Ver ALTER TABLE

ALTER TABLE foo NOCHECK CONSTRAINT ALL

o

ALTER TABLE foo NOCHECK CONSTRAINT CK_foo_column

Las claves primarias y las restricciones únicas no se pueden deshabilitar, pero esto debería estar bien si lo entendí correctamente.

Answer 3

Y, si desea verificar que NO ha roto sus relaciones y no ha introducido huérfanos, una vez que haya vuelto a armar sus cheques, es decir,

ALTER TABLE foo CHECK CONSTRAINT ALL

o

ALTER TABLE foo CHECK CONSTRAINT FK_something

entonces puede volver a ejecutar y hacer una actualización en contra de cualquier columna marcada como esta:

UPDATE myUpdatedTable SET someCol = someCol, fkCol = fkCol, etc = etc

Y cualquier error en ese punto se deberá a la falta de cumplimiento de las restricciones.

Answer 4

Deshabilitar y habilitar todas las claves externas

CREATE PROCEDURE pr_Disable_Triggers_v2
@disable BIT = 1
AS
DECLARE @sql VARCHAR(500)
, @tableName VARCHAR(128)
, @tableSchema VARCHAR(128)
-- List of all tables
DECLARE triggerCursor CURSOR FOR
SELECT t.TABLE_NAME AS TableName
, t.TABLE_SCHEMA AS TableSchema
FROM INFORMATION_SCHEMA.TABLES t
ORDER BY t.TABLE_NAME, t.TABLE_SCHEMA
OPEN triggerCursor
FETCH NEXT FROM triggerCursor INTO @tableName, @tableSchema
WHILE ( @@FETCH_STATUS = 0 )
BEGIN
SET @sql = ''ALTER TABLE '' + @tableSchema + ''.['' + @tableName + ''] ''
IF @disable = 1
SET @sql = @sql + '' DISABLE TRIGGER ALL''
ELSE
SET @sql = @sql + '' ENABLE TRIGGER ALL''
PRINT ''Executing Statement - '' + @sql
EXECUTE ( @sql )
FETCH NEXT FROM triggerCursor INTO @tableName, @tableSchema
END
CLOSE triggerCursor
DEALLOCATE triggerCursor

Primero, el cursor foreignKeyCursor se declara como la instrucción SELECT que reúne la lista de claves externas y sus nombres de tabla. A continuación, se abre el cursor y se ejecuta la instrucción FETCH inicial. Esta instrucción FETCH leerá los datos de la primera fila en las variables locales @foreignKeyName y @tableName. Al recorrer un cursor, puede marcar @@ FETCH_STATUS para un valor de 0, lo que indica que la recuperación fue exitosa. Esto significa que el ciclo continuará avanzando para que pueda obtener cada clave externa sucesiva del conjunto de filas. @@ FETCH_STATUS está disponible para todos los cursores en la conexión. Entonces, si está recorriendo varios cursores, es importante verificar el valor de @@ FETCH_STATUS en la instrucción que sigue inmediatamente a la instrucción FETCH. @@ FETCH_STATUS reflejará el estado de la operación FETCH más reciente en la conexión. Los valores válidos para @@ FETCH_STATUS son:

0 = FETCH fue exitoso
-1 = FETCH no tuvo éxito
-2 = falta la fila que se obtuvo

Dentro del ciclo, el código crea el comando ALTER TABLE de forma diferente según si la intención es deshabilitar o habilitar la restricción de clave externa (usando la palabra clave CHECK o NOCHECK). La declaración se imprime como un mensaje para que se pueda observar su progreso y luego se ejecuta la declaración. Finalmente, cuando todas las filas se han repetido, el procedimiento almacenado se cierra y desasigna el cursor.

consulte Desactivación de restricciones y desencadenantes de MSDN Magazine

Answer 5

-- Disable the constraints on a table called tableName:
ALTER TABLE tableName NOCHECK CONSTRAINT ALL
-- Re-enable the constraints on a table called tableName:
ALTER TABLE tableName WITH CHECK CHECK CONSTRAINT ALL
---------------------------------------------------------
-- Disable constraints for all tables:
EXEC sp_msforeachtable ''ALTER TABLE ? NOCHECK CONSTRAINT all''
-- Re-enable constraints for all tables:
EXEC sp_msforeachtable ''ALTER TABLE ? WITH CHECK CHECK CONSTRAINT all''
---------------------------------------------------------