Problema
As chaves estrangeiras (FK) são projetados para manter a integridade referencial em seu banco de dados. Quando usado corretamente FKS permitem ter certeza de que seus dados estão intactos e há registros de nenhum órfão. No outro lado do uso de FKS para manter a integridade referencial, eles também se tornam um problema quando você precisa mudar estruturas de tabela ou modificar temporariamente dados que possam violar a restrição de chave estrangeira. Outras dicas foram escritos que mostram como identificar seus FKS e por que você deve usá-los, mas o que é a melhor abordagem para manipular restrições FK para fazer a estrutura ou alterações de dados?
Solução
Abaixo está um script que pode ser usado para encontrar todas as chaves estrangeiras que fazem referência a tabela primária que você deseja trabalhar. Neste roteiro você fornecer o nome da tabela eo nome do esquema (proprietário do objeto). O script irá retornar uma lista de afirmações que podem ser copiados e colados em uma janela de consulta para fazer essas mudanças.
O roteiro também leva três valores de parâmetros diferentes, dependendo da ação que você deseja tomar:
DISABLE - isso irá criar o comando para desativar todas as restrições FK que fazem referência a tabela que você está trabalhando com
ENABLE- isso irá criar o comando para ativar todas as restrições FK que fazem referência a tabela que você está trabalhando com
DROP - isso irá criar um comando para retirar todas as restrições FK e criar um comando para criar todas as restrições FK que fazem referência a tabela está trabalhando com
Os valores abaixo de usar uma tabela no banco de dados Corpore, assim você pode simplesmente copiar e colar este código e executar esse exemplo contra o banco de dados.
SET NOCOUNT ON
DECLARE @operation VARCHAR(10) DECLARE @tableName sysname
DECLARE @schemaName sysname
SET @operation = 'DROP' ---- ENABLE, DISABLE, DROP SET @tableName = 'GCCUSTO' SET @schemaName = 'dbo'
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) = @schemaNameOPEN 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
-- criar declaração para permitir FK
IF @operation = 'ENABLE'
BEGIN
SET @cmd = 'ALTER TABLE [' + @FKTABLE_OWNER + '].[' + @FKTABLE_NAME
+ '] CHECK CONSTRAINT [' + @FK_NAME + ']'
PRINT @cmd
END
-- create statement for disabling FK
IF @operation = 'DISABLE'
BEGIN
SET @cmd = 'ALTER TABLE [' + @FKTABLE_OWNER + '].[' + @FKTABLE_NAME
+ '] NOCHECK CONSTRAINT [' + @FK_NAME + ']'
PRINT @cmd
END
-- criar declaração para excluir uma FK e também para a recriação de FK
IF @operation = 'DROP'
BEGIN
-- comando drop
SET @cmd = 'ALTER TABLE [' + @FKTABLE_OWNER + '].[' + @FKTABLE_NAME
+ '] DROP CONSTRAINT [' + @FK_NAME + ']'
PRINT @cmd
-- criar processo
DECLARE @FKCOLUMNS VARCHAR(1000), @PKCOLUMNS VARCHAR(1000), @COUNTER INT
-- cria o cursor para pegar a coluna do FK
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
-- gera o comando para criar o FK
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 