¿Cómo se pueden deshabilitar temporalmente las restricciones de clave externa usando T-SQL?
¿Se admiten deshabilitar y habilitar restricciones de clave externa en SQL Server? ¿O mi única opción es dropy luego volver a cumplircreate las restricciones?
Si desea deshabilitar todas las restricciones en la base de datos, simplemente ejecute este código:
-- disable all constraints
EXEC sp_MSforeachtable "ALTER TABLE ? NOCHECK CONSTRAINT all"
Para volver a encenderlos, ejecute: (la impresión es opcional, por supuesto, y solo enumera las tablas)
-- enable all constraints
exec sp_MSforeachtable @command1="print '?'", @command2="ALTER TABLE ? WITH CHECK CHECK CONSTRAINT all"
Lo encuentro útil al completar datos de una base de datos a otra. Es un enfoque mucho mejor que eliminar las restricciones. Como mencionó, resulta útil al colocar todos los datos en la base de datos y volver a llenarlos (por ejemplo, en un entorno de prueba).
Si está eliminando todos los datos, esta solución puede resultarle útil.
Además, a veces es útil desactivar todos los activadores; puedes ver la solución completa aquí .
(Copiado de http://www.sqljunkies.com/WebLog/roman/archive/2005/01/30/7037.aspx , que ahora está archivado en Wayback Machine )
Las restricciones de clave externa y las restricciones de verificación son muy útiles para hacer cumplir la integridad de los datos y las reglas comerciales. Sin embargo, hay ciertos escenarios en los que es útil desactivarlos temporalmente porque su comportamiento no es necesario o podría hacer más daño que bien. A veces desactivo la verificación de restricciones en las tablas durante las cargas de datos de fuentes externas o cuando necesito crear un script para soltar/recrear una tabla y volver a cargar los datos en la tabla. Normalmente lo hago en escenarios en los que no quiero que falle un proceso que requiere mucho tiempo porque una o algunas de muchos millones de filas contienen datos incorrectos. Pero siempre vuelvo a activar las restricciones una vez finalizado el proceso y, en algunos casos, también ejecuto comprobaciones de integridad de los datos importados.
Si desactiva una restricción de clave externa, podrá insertar un valor que no existe en la tabla principal. Si desactiva una restricción de verificación, podrá poner un valor en una columna como si la restricción de verificación no estuviera allí. A continuación se muestran algunos ejemplos de cómo deshabilitar y habilitar restricciones de tabla:
-- 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
Para deshabilitar la restricción tienes ALTERla tabla usando NOCHECK
ALTER TABLE [TABLE_NAME] NOCHECK CONSTRAINT [ALL|CONSTRAINT_NAME]
Para habilitarlo, debe usar doble VERIFICACIÓN :
ALTER TABLE [TABLE_NAME] WITH CHECK CHECK CONSTRAINT [ALL|CONSTRAINT_NAME]
- Preste atención al doble CHECK CHECK al habilitar.
- TODOS significa para todas las restricciones de la tabla.
Una vez completado, si necesita verificar el estado, use este script para enumerar el estado de la restricción. Será de mucha ayuda:
SELECT (CASE
WHEN OBJECTPROPERTY(CONSTID, 'CNSTISDISABLED') = 0 THEN 'ENABLED'
ELSE 'DISABLED'
END) AS STATUS,
OBJECT_NAME(CONSTID) AS CONSTRAINT_NAME,
OBJECT_NAME(FKEYID) AS TABLE_NAME,
COL_NAME(FKEYID, FKEY) AS COLUMN_NAME,
OBJECT_NAME(RKEYID) AS REFERENCED_TABLE_NAME,
COL_NAME(RKEYID, RKEY) AS REFERENCED_COLUMN_NAME
FROM SYSFOREIGNKEYS
ORDER BY TABLE_NAME, CONSTRAINT_NAME,REFERENCED_TABLE_NAME, KEYNO
Su mejor opción es BOTAR y CREAR restricciones de clave externa.
No encontré ejemplos en esta publicación que funcionaran para mí "tal cual", uno no funcionaría si las claves externas hacen referencia a esquemas diferentes, el otro no funcionaría si la clave externa hace referencia a varias columnas. Este script considera tanto múltiples esquemas como múltiples columnas por clave externa.
Aquí está el script que genera declaraciones "ADD CONSTRAINT", para varias columnas las separará por coma ( asegúrese de guardar este resultado antes de ejecutar declaraciones DROP ):
PRINT N'-- CREATE FOREIGN KEY CONSTRAINTS --';
SET NOCOUNT ON;
SELECT '
PRINT N''Creating '+ const.const_name +'...''
GO
ALTER TABLE ' + const.parent_obj + '
ADD CONSTRAINT ' + const.const_name + ' FOREIGN KEY (
' + const.parent_col_csv + '
) REFERENCES ' + const.ref_obj + '(' + const.ref_col_csv + ')
GO'
FROM (
SELECT QUOTENAME(fk.NAME) AS [const_name]
,QUOTENAME(schParent.NAME) + '.' + QUOTENAME(OBJECT_name(fkc.parent_object_id)) AS [parent_obj]
,STUFF((
SELECT ',' + QUOTENAME(COL_NAME(fcP.parent_object_id, fcp.parent_column_id))
FROM sys.foreign_key_columns AS fcP
WHERE fcp.constraint_object_id = fk.object_id
FOR XML path('')
), 1, 1, '') AS [parent_col_csv]
,QUOTENAME(schRef.NAME) + '.' + QUOTENAME(OBJECT_NAME(fkc.referenced_object_id)) AS [ref_obj]
,STUFF((
SELECT ',' + QUOTENAME(COL_NAME(fcR.referenced_object_id, fcR.referenced_column_id))
FROM sys.foreign_key_columns AS fcR
WHERE fcR.constraint_object_id = fk.object_id
FOR XML path('')
), 1, 1, '') AS [ref_col_csv]
FROM sys.foreign_key_columns AS fkc
INNER JOIN sys.foreign_keys AS fk ON fk.object_id = fkc.constraint_object_id
INNER JOIN sys.objects AS oParent ON oParent.object_id = fkc.parent_object_id
INNER JOIN sys.schemas AS schParent ON schParent.schema_id = oParent.schema_id
INNER JOIN sys.objects AS oRef ON oRef.object_id = fkc.referenced_object_id
INNER JOIN sys.schemas AS schRef ON schRef.schema_id = oRef.schema_id
GROUP BY fkc.parent_object_id
,fkc.referenced_object_id
,fk.NAME
,fk.object_id
,schParent.NAME
,schRef.NAME
) AS const
ORDER BY const.const_name
Aquí está el script que genera declaraciones "DROP CONSTRAINT":
PRINT N'-- DROP FOREIGN KEY CONSTRAINTS --';
SET NOCOUNT ON;
SELECT '
PRINT N''Dropping ' + fk.NAME + '...''
GO
ALTER TABLE [' + sch.NAME + '].[' + OBJECT_NAME(fk.parent_object_id) + ']' + ' DROP CONSTRAINT ' + '[' + fk.NAME + ']
GO'
FROM sys.foreign_keys AS fk
INNER JOIN sys.schemas AS sch ON sch.schema_id = fk.schema_id
ORDER BY fk.NAME
