update - ¿MS SQL "ON DELETE CASCADE" varias claves externas que apuntan a la misma tabla?

sql server add constraint foreign key cascade

Question

Howdy, tengo un problema en el que necesito una cascada en varias claves externas que apuntan a la misma tabla ...

[Insights]
| ID | Title        |
| 1  | Monty Python |
| 2  | Spamalot     | 

[BroaderInsights_Insights]
| broaderinsight_id | insight_id |
| 1                 | 2          |

Básicamente, cuando se borra el registro uno o dos en la tabla de perspectivas, necesito que también se elimine la relación.

He intentado esto:

 CREATE TABLE broader_insights_insights(id INT NOT NULL IDENTITY(1,1),
   broader_insight_id INT NOT NULL REFERENCES insights(id) ON DELETE CASCADE,
   insight_id INT NOT NULL REFERENCES insights(id) ON DELETE CASCADE,
   PRIMARY KEY(id))
Go

Esto da como resultado la advertencia de que la cascada "puede causar ciclos o múltiples rutas en cascada"

Así que he intentado agregar una cascada solo al insight_id y esto resulta en:

"La sentencia DELETE entró en conflicto con la restricción REFERENCE"

¿Algunas ideas?

Gracias

Daniel

Answer 1

Tendrá que implementar esto como un desencadenante de eliminación INSTEAD OF en las perspectivas, para que funcione. Algo como:

create trigger T_Insights_D
on Insights
instead of delete
as
    set nocount on
    delete from broader_insights_insights
    where insight_id in (select ID from deleted) or
    broader_insight_id in (select ID from deleted)

    delete from Insights where ID in (select ID from deleted)

Con frecuencia, con las eliminaciones en cascada y muchas claves externas, debe dedicar tiempo a elaborar una orden "en cascada" para que la eliminación que se produce en la parte superior de un "árbol" se conecte en cascada a las tablas de referencia. Pero eso no es posible en este caso.