sql-server - with - script cascade delete sql server




Quando/Por que usar o Cascading no SQL Server? (10)

Ao configurar chaves estrangeiras no SQL Server, em que circunstâncias você deve ter cascata na exclusão ou atualização e qual é o raciocínio por trás disso?

Isso provavelmente se aplica a outros bancos de dados também.

Eu estou procurando, acima de tudo, exemplos concretos de cada cenário, de preferência de alguém que os usou com sucesso.


ON Excluir Cascata:

Quando você deseja que as linhas na tabela filho sejam excluídas Se a linha correspondente for excluída na tabela pai.

Se em cascata delete não for usado, um erro será gerado para a integridade referencial .

ON Atualizar Cascade:

Quando você quer que a mudança na chave primária seja atualizada na chave estrangeira


As exclusões em cascata são extremamente úteis ao implementar entidades de tipo e subtipo lógicas em um banco de dados físico.

Quando são usadas tabelas de super-tipos e subtipos separadas para implementar fisicamente super-tipos / sub-tipos (ao contrário de agrupar todos os atributos de subtipo em uma única tabela de super-tipo físico), existe um Um relacionamento entre essas tabelas e o problema torna-se como manter as chaves primárias 100% sincronizadas entre essas tabelas.

As exclusões em cascata podem ser uma ferramenta muito útil para:

1) Certifique-se de que a exclusão de um registro de supertipo também exclua o registro de subtipo único correspondente.

2) Certifique-se de que qualquer exclusão de um registro de subtipo também exclua o registro de supertipo. Isso é conseguido implementando um gatilho de exclusão "em vez de" na tabela de subtipos que vai e exclui o registro de supertipo correspondente, que, por sua vez, em cascata, exclui o registro de subtipo.

Usar as exclusões em cascata dessa maneira garante que não existam nenhum tipo de super-tipo ou subtipo órfão, independentemente de você excluir primeiro o registro de supertipo ou o registro de subtipo.


Eu faço muito trabalho de banco de dados e raramente encontro exclusões em cascata úteis. A única vez que os utilizei de forma eficaz é em um banco de dados de relatórios que é atualizado por um trabalho noturno. Garanto que todos os dados alterados são importados corretamente, excluindo todos os registros de nível superior que foram alterados desde a última importação e, em seguida, reimporte os registros modificados e qualquer item relacionado a eles. Isso me poupa de ter que escrever muitas exclusões complicadas que olham de baixo para o topo do meu banco de dados.

Eu não considero que as exclusões em cascata sejam tão ruins quanto os gatilhos, pois elas apenas excluem os dados, os gatilhos podem ter todos os tipos de coisas desagradáveis ​​dentro deles.

Em geral, evito completamente as Exclusões reais e uso exclusões lógicas (ou seja, ter uma coluna de bits chamada isDeleted que é configurada como true).


Eu nunca uso exclusões em cascata.

Se eu quiser algo removido do banco de dados, quero informar explicitamente ao banco de dados o que eu quero remover.

Claro que eles são uma função disponível no banco de dados e pode haver momentos em que não há problema em usá-los, por exemplo, se você tiver uma tabela 'order' e uma tabela 'orderItem', você pode limpar os itens quando excluir um ordem.

Eu gosto da clareza que eu recebo ao fazer isso em código (ou procedimento armazenado) ao invés de 'mágica' acontecer.

Pela mesma razão, também não sou fã de gatilhos.

Algo a se notar é que, se você excluir um 'pedido', receberá um relatório '1 linha afetada', mesmo que a exclusão em cascata tenha removido 50 'orderItem'.


Eu trabalho muito com exclusões em cascata.

É bom saber quem trabalha contra o banco de dados pode nunca deixar nenhum dado indesejado. Se as dependências crescerem, altero as restrições no diagrama no Management Studio e não preciso ajustar sp ou dataacces.

Dito isto, eu tenho um problema com exclusões em cascata e isso é referências circulares. Isso geralmente leva a partes do banco de dados que não possuem exclusões em cascata.


Eu, como todos os outros aqui, acho que as exclusões em cascata são realmente apenas marginalmente úteis (não é muito trabalho excluir dados referenciados em outras tabelas - se há muitas tabelas, você simplesmente automatiza isso com um script) mas realmente irritante quando alguém acidentalmente em cascata exclui alguns dados importantes que são difíceis de restaurar.

O único caso em que eu usaria seria se os dados da tabela forem altamente controlados (por exemplo, permissões limitadas) e somente atualizados ou excluídos por meio de um processo controlado (como uma atualização de software) que tenha sido verificado.


Resumo do que eu vi até agora:

  • Algumas pessoas não gostam de cascatear.

Excluir cascata

  • A exclusão em cascata pode fazer sentido quando a semântica do relacionamento pode envolver uma descrição exclusiva "é parte de ". Por exemplo, um registro OrderLine faz parte de sua ordem pai e os OrderLines nunca serão compartilhados entre vários pedidos. Se a Ordem desaparecesse, o OrderLine deveria, e uma linha sem um pedido seria um problema.
  • O exemplo canônico para Cascade Delete é SomeObject e SomeObjectItems, onde não faz sentido algum registro de itens existir sem um registro principal correspondente.
  • Você não deve usar o Cascade Delete se estiver preservando o histórico ou usando uma "exclusão soft / lógica", em que apenas define uma coluna de bits excluída como 1 / true.

Atualização em cascata

  • A atualização em cascata pode fazer sentido quando você usa uma chave real em vez de uma chave substituta (coluna de identidade / incremento automático) nas tabelas.
  • O exemplo canônico da Atualização em Cascata é quando você tem uma chave estrangeira mutável, como um nome de usuário que pode ser alterado.
  • Você não deve usar o Cascade Update com chaves que são colunas de identidade / autoincremento.
  • A atualização em cascata é melhor usada em conjunto com uma restrição exclusiva.

Quando usar cascata

  • Você pode querer obter uma confirmação extra forte do usuário antes de permitir uma operação em cascata, mas isso depende do seu aplicativo.
  • Cascading pode colocar você em problemas se você configurar suas chaves estrangeiras errado. Mas você deve estar bem se você fizer isso direito.
  • Não é aconselhável usar cascateamento antes de entendê-lo completamente. No entanto, é um recurso útil e, portanto, vale a pena dedicar um tempo para entender.

Se você estiver trabalhando em um sistema com muitos módulos diferentes em versões diferentes, pode ser muito útil se os itens excluídos em cascata fizerem parte do / propriedade do detentor do PK. Além disso, todos os módulos exigiriam patches imediatos para limpar seus itens dependentes antes de excluir o proprietário PK ou a relação de chave estrangeira seria omitida completamente, possivelmente deixando toneladas de lixo no sistema se a limpeza não fosse executada corretamente.

Acabei de introduzir a exclusão em cascata para uma nova tabela de interseções entre duas tabelas já existentes (a interseção para excluir somente), após a exclusão em cascata ter sido desencorajada por algum tempo. Também não é muito ruim se os dados forem perdidos.

É, no entanto, uma coisa ruim em tabelas de lista parecidas com enum: alguém exclui a entrada 13 - amarela da tabela "colors", e todos os itens amarelos no banco de dados são deletados. Além disso, algumas vezes, elas são atualizadas de uma maneira que exclui todas as inserções, levando à integridade referencial totalmente omitida. É claro que está errado, mas como você mudará um software complexo que está sendo executado há muitos anos, com a introdução da integridade referencial verdadeira estando em risco de efeitos colaterais inesperados?

Outro problema é quando os valores originais da chave estrangeira devem ser mantidos mesmo após a chave primária ter sido excluída. É possível criar uma coluna para exclusão e uma opção ON DELETE SET NULL para o FK original, mas isso novamente requer gatilhos ou código específico para manter o valor da chave redundante (exceto após a exclusão PK).


Um motivo para colocar uma exclusão em cascata (em vez de fazê-lo no código) é melhorar o desempenho.

Caso 1: com uma exclusão em cascata

 DELETE FROM table WHERE SomeDate < 7 years ago;

Caso 2: sem uma exclusão em cascata

 FOR EACH R IN (SELECT FROM table WHERE SomeDate < 7 years ago) LOOP
   DELETE FROM ChildTable WHERE tableId = R.tableId;
   DELETE FROM table WHERE tableId = R.tableid;
   /* More child tables here */
 NEXT

Em segundo lugar, quando você adiciona uma tabela filha extra com uma exclusão em cascata, o código no Caso 1 continua funcionando.

Eu colocaria apenas uma cascata onde a semântica do relacionamento é "parte de". Caso contrário, algum idiota excluirá metade do seu banco de dados quando você fizer isso:

DELETE FROM CURRENCY WHERE CurrencyCode = 'USD'

Uma exclusão ou atualização para S que remove um valor de chave estrangeira encontrado em algumas tuplas de R pode ser manipulada de uma das três maneiras:

  1. Rejeição
  2. Propagação
  3. anulação.

Propagação é conhecida como cascata.

Existem dois casos:

‣ Se uma tupla em S foi excluída, exclua as tuplas R que se referiram a ela.

‣ Se uma tupla em S foi atualizada, atualize o valor nas tuplas R que se referem a ela.





cascade