As chaves estrangeiras (FK) são um componente essencial para manter a integridade referencial dentro de um banco de dados. Elas garantem que os relacionamentos de dados sejam mantidos e evitam registros órfãos. No entanto, quando se trata de fazer alterações na estrutura das tabelas ou modificar temporariamente os dados que podem violar as restrições de chave estrangeira, as FKs podem se tornar um desafio. Neste artigo, exploraremos a melhor abordagem para manipular restrições de chave estrangeira no SQL Server.
Antes de mergulharmos na solução, vamos recapitular rapidamente a importância das chaves estrangeiras e como identificá-las em seu banco de dados. As chaves estrangeiras estabelecem relacionamentos entre tabelas, referenciando a chave primária de outra tabela. Elas ajudam a manter a integridade dos dados e a garantir a consistência dos dados.
Para identificar todas as chaves estrangeiras em um banco de dados, você pode usar o seguinte script:
SELECT
OBJECT_NAME(fk.parent_object_id) AS [Nome da Tabela],
COL_NAME(fkc.parent_object_id, fkc.parent_column_id) AS [Nome da Coluna],
OBJECT_NAME(fk.referenced_object_id) AS [Tabela Referenciada],
COL_NAME(fkc.referenced_object_id, fkc.referenced_column_id) AS [Coluna Referenciada]
FROM
sys.foreign_keys AS fk
INNER JOIN
sys.foreign_key_columns AS fkc ON fk.object_id = fkc.constraint_object_id
ORDER BY
[Nome da Tabela], [Nome da Coluna];
Agora, vamos passar para a solução para manipular restrições de chave estrangeira. O script abaixo demonstra como habilitar, desabilitar, excluir e recriar FKs com base em uma tabela de chave primária:
SET NOCOUNT ON
DECLARE @operation VARCHAR(10)
DECLARE @tableName sysname
DECLARE @schemaName sysname
SET @operation = 'DROP' -- ENABLE, DISABLE, DROP
SET @tableName = 'SpecialOfferProduct'
SET @schemaName = 'Sales'
DECLARE @cmd NVARCHAR(1000)
-- Cursor e consulta para buscar informações de FK
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) AS Pk_table_schema
FROM
sys.foreign_keys Fk
LEFT OUTER JOIN
sys.tables TbR ON TbR.OBJECT_ID = Fk.referenced_object_id
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
-- Criar comando para habilitar FK
IF @operation = 'ENABLE'
BEGIN
SET @cmd = 'ALTER TABLE [' + @FKTABLE_OWNER + '].[' + @FKTABLE_NAME
+ '] CHECK CONSTRAINT [' + @FK_NAME + ']'
PRINT @cmd
END
-- Criar comando para desabilitar FK
IF @operation = 'DISABLE'
BEGIN
SET @cmd = 'ALTER TABLE [' + @FKTABLE_OWNER + '].[' + @FKTABLE_NAME
+ '] NOCHECK CONSTRAINT [' + @FK_NAME + ']'
PRINT @cmd
END
-- Criar comando para excluir e recriar FK
IF @operation = 'DROP'
BEGIN
-- Comando de exclusão
SET @cmd = 'ALTER TABLE [' + @FKTABLE_OWNER + '].[' + @FKTABLE_NAME
+ '] DROP CONSTRAINT [' + @FK_NAME + ']'
PRINT @cmd
-- Processo de recriação
DECLARE @FKCOLUMNS VARCHAR(1000), @PKCOLUMNS VARCHAR(1000), @COUNTER INT
-- Cursor para obter colunas 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
-- Gerar comando de criação de 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
Ao modificar os valores das variáveis @operation, @tableName e @schemaName, você pode habilitar, desabilitar, excluir ou recriar restrições de chave estrangeira para uma tabela específica em um esquema específico.
Lembre-se de ter cuidado ao manipular restrições de chave estrangeira, pois quaisquer alterações podem afetar a integridade dos dados. Sempre teste seus scripts em um ambiente controlado antes de aplicá-los a um banco de dados de produção.
Isso é tudo! Agora você tem uma melhor compreensão de como manipular restrições de chave estrangeira no SQL Server. Usando o script fornecido, você pode facilmente habilitar, desabilitar, excluir ou recriar restrições de chave estrangeira com base em seus requisitos específicos.