Blog para aprender un poco sobre tecnologías SQL Server tales como SSIS SSRS SSAS SSMS T-SQL, adicional compartir libros proyectos y ideas sobre mantenimiento de base de datos, optimizacion y configuracion

viernes, 7 de julio de 2017

Como desactivar claves foráneas de una tabla

Como desactivar claves foráneas de una tabla  

Un día, una persona comento que si es posible desactivar las claves foráneas de una tabla mientras se realiza una operación de DELETE, nunca he tenido esa necesidad sin embargo, el requerimiento es curioso la tabla tiene 100 relaciones o mas, y al realizar un DELETE, de un gran conjunto de datos es muy lento, por lo cual se creo un SP que pueda realizar dicho requerimiento
Resultado de imagen para CLAVES FORANEAS

/* CREAS EL PROCEDIMIENTO ALMANCENADO QUE ESTA EN EL CODIGO Y PARA ELIMINAR USAS LOS PASOS QUE TE ENUMERO*/
--***********************************************************************************************************
EXECUTE OperationSQLForeignKey 'DISABLE','AQUI_TU_TABLA','AQUI_SU_ESQUEMA' 
--***********************************************************************************************************
DELETE FROM AQUI_TU_TABLA WHERE 'LO QUE QUIERAS'
 --***********************************************************************************************************
EXECUTE OperationSQLForeignKey 'ENABLE','AQUI_TU_TABLA','AQUI_SU_ESQUEMA' 
--***********************************************************************************************************
--****************Procedimiento para realizar ENABLE OR DISABLE las claves foráneas de una tabla ***********************
--***********************************************************************************************************
CREATE PROCEDURE OperationSQLForeignKey (
@operation VARCHAR(10),
@tableName sysname,
@schemaName sysname
) AS BEGIN
SET
NOCOUNT ON DECLARE @cmd NVARCHAR(1000) DECLARE @FK_NAME sysname,
@FK_OBJECTID INT,
@FK_DISABLED INT,
@FK_NOT_FOR_REPLICATION INT,
@DELETE_RULE smallint,
@UPDATE_RULE smallint,
@FKTABLE_NAME sysname,
@FKTABLE_OWNER sysname,
@PKTABLE_NAME sysname,
@PKTABLE_OWNER sysname,
@FKCOLUMN_NAME sysname,
@PKCOLUMN_NAME sysname,
@CONSTRAINT_COLID INT DECLARE cursor_fkeys CURSOR FOR
SELECT
Fk.name,
Fk.OBJECT_ID,
Fk.is_disabled,
Fk.is_not_for_replication,
Fk.delete_referential_action,
Fk.update_referential_action,
OBJECT_NAME(Fk.parent_object_id) AS Fk_table_name,
schema_name(Fk.schema_id) AS Fk_table_schema,
TbR.name AS Pk_table_name,
schema_name(TbR.schema_id) Pk_table_schema
FROM
sys.foreign_keys Fk
LEFT OUTER JOIN sys.tables TbR ON TbR.OBJECT_ID = Fk.referenced_object_id --inner join
WHERE
TbR.name = @tableName
AND schema_name(TbR.schema_id) = @schemaName OPEN cursor_fkeys FETCH NEXT
FROM
cursor_fkeys INTO @FK_NAME,
@FK_OBJECTID,
@FK_DISABLED,
@FK_NOT_FOR_REPLICATION,
@DELETE_RULE,
@UPDATE_RULE,
@FKTABLE_NAME,
@FKTABLE_OWNER,
@PKTABLE_NAME,
@PKTABLE_OWNER WHILE @@FETCH_STATUS = 0 BEGIN IF @operation = 'ENABLE' BEGIN
SET
@cmd = 'ALTER TABLE [' + @FKTABLE_OWNER + '].[' + @FKTABLE_NAME + '] CHECK CONSTRAINT [' + @FK_NAME + ']' PRINT @cmd EXEC sp_executesql @cmd END IF @operation = 'DISABLE' BEGIN
SET
@cmd = 'ALTER TABLE [' + @FKTABLE_OWNER + '].[' + @FKTABLE_NAME + '] NOCHECK CONSTRAINT [' + @FK_NAME + ']' PRINT @cmd EXEC sp_executesql @cmd END IF @operation = 'DROP' BEGIN
SET
@cmd = 'ALTER TABLE [' + @FKTABLE_OWNER + '].[' + @FKTABLE_NAME + '] DROP CONSTRAINT [' + @FK_NAME + ']' PRINT @cmd EXEC sp_executesql @cmd DECLARE @FKCOLUMNS VARCHAR(1000),
@PKCOLUMNS VARCHAR(1000),
@COUNTER INT DECLARE cursor_fkeyCols CURSOR FOR
SELECT
COL_NAME(
Fk.parent_object_id, Fk_Cl.parent_column_id
) AS Fk_col_name,
COL_NAME(
Fk.referenced_object_id, Fk_Cl.referenced_column_id
) AS Pk_col_name
FROM
sys.foreign_keys Fk
LEFT OUTER JOIN sys.tables TbR ON TbR.OBJECT_ID = Fk.referenced_object_id
INNER JOIN sys.foreign_key_columns Fk_Cl ON Fk_Cl.constraint_object_id = Fk.OBJECT_ID
WHERE
TbR.name = @tableName
AND schema_name(TbR.schema_id) = @schemaName
AND Fk_Cl.constraint_object_id = @FK_OBJECTID
ORDER BY
Fk_Cl.constraint_column_id OPEN cursor_fkeyCols FETCH NEXT
FROM
cursor_fkeyCols INTO @FKCOLUMN_NAME,
@PKCOLUMN_NAME
SET
@COUNTER = 1
SET
@FKCOLUMNS = ''
SET
@PKCOLUMNS = '' WHILE @@FETCH_STATUS = 0 BEGIN IF @COUNTER > 1 BEGIN
SET
@FKCOLUMNS = @FKCOLUMNS + ','
SET
@PKCOLUMNS = @PKCOLUMNS + ',' END
SET
@FKCOLUMNS = @FKCOLUMNS + '[' + @FKCOLUMN_NAME + ']'
SET
@PKCOLUMNS = @PKCOLUMNS + '[' + @PKCOLUMN_NAME + ']'
SET
@COUNTER = @COUNTER + 1 FETCH NEXT
FROM
cursor_fkeyCols INTO @FKCOLUMN_NAME,
@PKCOLUMN_NAME END CLOSE cursor_fkeyCols DEALLOCATE cursor_fkeyCols
SET
@cmd = 'ALTER TABLE [' + @FKTABLE_OWNER + '].[' + @FKTABLE_NAME + '] WITH ' + CASE @FK_DISABLED WHEN 0 THEN ' CHECK ' WHEN 1 THEN ' NOCHECK ' END + ' ADD CONSTRAINT [' + @FK_NAME + '] FOREIGN KEY (' + @FKCOLUMNS + ') REFERENCES [' + @PKTABLE_OWNER + '].[' + @PKTABLE_NAME + '] (' + @PKCOLUMNS + ') ON UPDATE ' + CASE @UPDATE_RULE WHEN 0 THEN ' NO ACTION ' WHEN 1 THEN ' CASCADE ' WHEN 2 THEN ' SET_NULL ' END + ' ON DELETE ' + CASE @DELETE_RULE WHEN 0 THEN ' NO ACTION ' WHEN 1 THEN ' CASCADE ' WHEN 2 THEN ' SET_NULL ' END + '' + CASE @FK_NOT_FOR_REPLICATION WHEN 0 THEN '' WHEN 1 THEN ' NOT FOR REPLICATION ' END PRINT @cmd END FETCH NEXT
FROM
cursor_fkeys INTO @FK_NAME,
@FK_OBJECTID,
@FK_DISABLED,
@FK_NOT_FOR_REPLICATION,
@DELETE_RULE,
@UPDATE_RULE,
@FKTABLE_NAME,
@FKTABLE_OWNER,
@PKTABLE_NAME,
@PKTABLE_OWNER END CLOSE cursor_fkeys DEALLOCATE cursor_fkeys END 
--***********************************************************************************************************
--**************** END PROCEDURE ****************************************************************************
--***********************************************************************************************************

1 comentario: