Início / dba / Perguntas / 344880
Accepted
Carl Schmidt
Asked: 2025-01-23 08:48:39 +0800 CST

SQL Server: Como implementar relacionamento de 3 vias com cascatas

  • 772

Este é o esquema com o qual estou trabalhando:projeto de banco de dados

Aqui está o DBML para recriar em https://dbdiagram.io/d :

Table University {
  ID integer [primary key]
}

Table Professor {
  ID integer [primary key]
  UniversityID integer [primary key, ref: > University.ID]
}

Table Class {
  ID integer [primary key]
  UniversityID integer [primary key, ref: > University.ID]
  ProfessorID integer [null]
}

Ref: Class.(ProfessorID, UniversityID) - Professor.(ID, UniversityID)

Meu objetivo:

  • Uma universidade pode ter várias turmas e professores.
  • Uma classe pode ter um ou nenhum professor.
  • A exclusão de uma universidade exclui todos os seus professores e turmas
  • Excluir um Professor define Class.ProfessorID como NULL

O último objetivo está nos causando problemas. O SQL Server impede a adição de outra restrição de chave estrangeira em cascata devido a múltiplos caminhos em cascata, e um gatilho não pode funcionar porque:

  1. a consulta de exclusão é rejeitada devido à restrição de chave estrangeira no Professor, portanto, um gatilho FOR DELETE nunca será executado
  2. O SQL não pode criar um INSTEAD OF DELETE devido ao relacionamento em cascata com a Universidade

Como posso conseguir isso?

database-design

1 respostas

  1. Best Answer

    Com toda a honestidade, a maneira correta de fazer isso seria usar um procedimento armazenado.

    Minha experiência com projetos de bancos de dados do mundo real é que restrições declarativas são usadas de forma muito modesta (mesmo chaves estrangeiras frequentemente não são verificadas, mesmo se declaradas) e gatilhos são usados ​​ainda menos.

    O motivo é porque eles causam reatividade inesperada no mecanismo, onde o que parece ser para o usuário SQL uma simples instrução DML com um golpe mortal de martelo contra uma tabela explícita, se torna algo que pode afetar ou alterar uma enorme quantidade de dados relacionados que não foram especificados na instrução SQL.

    Um banco de dados com restrições em cascata e gatilhos mutantes pode se tornar algo extremamente difícil de investigar e raciocinar.

    Se usadas pelo designer do banco de dados, as restrições são mais bem usadas para executar verificações de integridade e evitar operações que são inválidas no formato escrito pelo usuário SQL. Por exemplo, se alguém tentar excluir um professor ainda vinculado a uma classe, a operação será rejeitada como inválida porque você está excluindo um professor que permanece vinculado.

    E como você já descobriu, mesmo com apenas três tabelas (ou seja, complexidade insignificante), as cascatas geralmente não combinam bem entre si e muitas vezes não são uma solução geral.

    Enquanto isso, é difícil projetar gatilhos para que funcionem corretamente em todas as circunstâncias, principalmente aquelas que causam mudanças (de modo que haja potencial para uma cascata).

    Então, meu conselho seria mover o trabalho para um procedimento armazenado que, se excluir um professor, primeiro verifica se o professor está sendo referenciado por alguma classe e, se estiver, quebra explicitamente esses links antes de prosseguir com a exclusão do professor.

    Alguém (incluindo você) que ler este procedimento armazenado mais tarde entenderá exatamente o que acontece quando um professor é excluído.

    O uso de um procedimento armazenado também oferece maior controle sobre a ordem das operações e muitas outras configurações que podem ser ajustadas em bancos de dados sob carga mais pesada.

    O uso de cascatas e gatilhos muitas vezes parece muito genérico, mas, na minha experiência, os casos em que eles são de fato apropriados são muito menores do que os casos em que parecem ingenuamente aplicáveis.

    Acredito que algo crucial é que os novatos tendem a tentar usá-los para dividir e conquistar a complexidade e evitar ter que escrever um procedimento explícito (ou lembrar de tudo o que precisa ser incluído naquele procedimento para torná-lo correto).

    O treinamento que os alunos recebem em outras linguagens às vezes encoraja isso, como decompor métodos grandes em menores em linguagens de propósito geral ou orientadas a objetos. Mas a analogia não se aplica ao uso de restrições e gatilhos em SQL, e é muito normal que um único procedimento em SQL se estenda por páginas.

    • 1

relate perguntas