llave - primary key y foreign key sql

¿Cuál es el problema con la clave externa en cascada de múltiples rutas y ciclos?

Question

En MSSQL 2005 acabo de golpear el mensaje de error infame:

La introducción de la restricción XXX de FOREIGN en la tabla YYY puede provocar ciclos o múltiples rutas en cascada. Especifique ON DELETE NO ACTION o ON UPDATE NO ACTION, o modifique otras restricciones FOREIGN KEY.

Ahora, StackOverflow tiene varios temas sobre este mensaje de error, por lo que ya tengo la solución (en mi caso, tendré que usar activadores), pero tengo curiosidad por saber por qué existe un problema de este tipo.

Como yo lo entiendo, hay básicamente dos escenarios que quieren evitar: un ciclo y varias rutas. Un ciclo sería donde dos tablas tienen claves externas en cascada entre sí. Bien, un ciclo puede abarcar varias tablas también, pero este es el caso básico y será más fácil de analizar.

Las rutas múltiples serían cuando TableA tenga claves foráneas para TableB y TableC, y TableB también tenga una clave foránea para TableC. De nuevo - este es el caso básico mínimo.

No puedo ver ningún problema que surja cuando un registro se elimine o actualice en cualquiera de esas tablas. Claro, es posible que deba consultar la misma tabla varias veces para ver qué registros deben actualizarse / eliminarse, pero ¿es eso realmente un problema? ¿Es este un problema de rendimiento?

En otros temas de SO, las personas van tan lejos como para etiquetar el uso de las cascadas como " risky " y afirman que " resolver las rutas en cascada es un problema complejo ". ¿Por qué? ¿Dónde está el riesgo? ¿Dónde está el problema?

Answer 1

Considere una tabla de empleados:

CREATE TABLE Employee
(
EmpID INTEGER NOT NULL PRIMARY KEY,
Name VARCHAR(40) NOT NULL,
MgrID INTEGER NOT NULL REFERENCES Employee(EmpID) ON DELETE CASCADE
);
INSERT INTO Employees( 1, "Bill", 1);
INSERT INTO Employees( 23, "Steve", 1);
INSERT INTO Employees(234212, "Helen", 23);

Ahora supongamos que Bill se retira:

DELETE FROM Employees WHERE Name = "Bill";

Ooooppps; ¡Todos fueron despedidos!

[ Podemos debatir si los detalles de la sintaxis son correctos; El concepto se mantiene, creo. ]

Answer 2

Creo que el problema es que cuando se hace una ruta "ON DELETE CASCADE" y la otra "ON DELETE RESTRICT", o "NO ACTION", el resultado (el resultado) no se puede eliminar. Depende de qué eliminación-disparador (esto también es un disparador, pero uno que no tienes que construir tú mismo) se ejecutará primero.

Answer 3

Creo que si usar o no una opción ON DELETE CASCADE es una cuestión del modelo de negocio que está implementando. Una relación entre dos objetos de negocio podría ser una simple "asociación", en la que ambos extremos de la relación están relacionados, pero, por lo demás, objetos independientes cuyo ciclo de vida es diferente y están controlados por otra lógica. Sin embargo, también hay relaciones de "agregación", en las que un objeto puede verse realmente como el "padre" o "propietario" de un objeto "secundario" o "detalle". Existe una noción aún más fuerte de una relación de "composición", donde un objeto existe únicamente como una composición de varias partes. En el caso de "asociación", por lo general no declarará una restricción de ON DELETE CASCADE. Sin embargo, para agregaciones o composiciones, ON DELETE CASCADE lo ayuda a asignar su modelo de negocio a la base de datos de manera más precisa y declarativa. Por eso me molesta que MS SQL Server restrinja el uso de esta opción a una única ruta en cascada. Si no me equivoco, muchos otros sistemas de bases de datos SQL ampliamente utilizados no imponen tales restricciones.

Answer 4

Estoy de acuerdo con que las cascadas son "riesgosas" y deben evitarse. (Personalmente, prefiero colocar en cascada los cambios manualmente en lugar de tener que el servidor SQL se encargue de ellos automáticamente). Esto también se debe a que incluso si el servidor SQL eliminara millones de filas, la salida aún se mostraría como

(1 fila (s) afectadas)

Answer 5

La razón por la que prohibimos el uso de la eliminación en cascada tiene que ver con el rendimiento y el bloqueo. Sí, no es tan malo cuando borra un registro, pero tarde o temprano tendrá que eliminar un gran grupo de registros y su base de datos se detendrá.

Si está eliminando suficientes registros, SQL Server puede escalar a un bloqueo de tabla y nadie puede hacer nada con la tabla hasta que finalice.

Recientemente trasladamos a uno de nuestros clientes a su propio servidor. Como parte del acuerdo, también tuvimos que eliminar todos los registros de ese cliente de nuestro servidor original. Eliminar toda su información en lotes (para no causar problemas con otros usuarios) tomó un par de meses. Si hubiéramos configurado la eliminación en cascada, la base de datos habría sido inaccesible para los demás clientes durante mucho tiempo, ya que se eliminaron millones de registros en una transacción y se bloquearon cientos de tablas hasta que se realizó la transacción.

También podría ver un escenario en el que podría haber ocurrido un interbloqueo en el uso de la eliminación en cascada porque no tenemos control sobre el orden que habría tomado la ruta en cascada y nuestra base de datos está algo desnormalizada y el ID de cliente aparece en la mayoría de las tablas. Por lo tanto, si bloqueaba la tabla que tenía una clave externa también a la tercera tabla, así como a la tabla cliente que estaba en una ruta diferente, posiblemente no podría verificar esa tabla para eliminarla de la tercera tabla porque esto es todo. Una transacción y los bloqueos no se liberarían hasta que se hiciera. Así que posiblemente no nos hubiera permitido configurar eliminaciones en cascada si hubiera visto la posibilidad de crear puntos muertos en la transacción.

Otra razón para evitar eliminaciones en cascada es que a veces la existencia de un registro secundario es razón suficiente para no eliminar el registro principal. Por ejemplo, si tiene una tabla de clientes y ese cliente ha tenido pedidos en el pasado, no querrá eliminarlo y perder la información sobre el pedido real.

Answer 6

Tiene una tabla secundaria con 2 rutas en cascada del mismo padre: una "eliminar", una "nula".

¿Qué tiene prioridad? ¿Qué esperas después? etc

Nota: Un disparador es un código y puede agregar algo de inteligencia o condiciones a una cascada.