extraer - ¿Cuándo/por qué usar Cascading en SQL Server?

collate sql server ejemplos (15)

ON Eliminar cascada:

Cuando desee que se eliminen las filas en la tabla secundaria Si la fila correspondiente se borra en la tabla principal.

Si no se utiliza la eliminación en cascada, se generará un error de integridad referencial .

ON Cascade de actualización:

Cuando desee que se actualice el cambio en la clave principal en clave externa

Al configurar claves externas en SQL Server, ¿bajo qué circunstancias debería tenerlas en cascada para eliminarlas o actualizarlas, y cuál es el razonamiento detrás de ellas?

Esto probablemente se aplique a otras bases de datos también.

Estoy buscando más ejemplos concretos de cada escenario, preferiblemente de alguien que los haya utilizado con éxito.

Hago mucho trabajo en la base de datos y rara vez encuentro útiles las eliminaciones en cascada. La única vez que los he usado de manera efectiva es en una base de datos de informes que se actualiza con un trabajo nocturno. Me aseguro de que cualquier información modificada se importe correctamente eliminando los registros de nivel superior que hayan cambiado desde la última importación, y luego vuelva a importar los registros modificados y todo lo relacionado con ellos. Me ahorra tener que escribir muchas eliminaciones complicadas que miran de abajo hacia arriba en mi base de datos.

No considero que las eliminaciones en cascada sean tan malas como los desencadenantes, ya que solo eliminan datos, los desencadenantes pueden tener todo tipo de cosas desagradables dentro.

En general, evito por completo las eliminaciones reales y uso eliminaciones lógicas (es decir, tener una columna de bit llamada isDeleted que se establece en verdadero) en su lugar.

Intento evitar las eliminaciones o actualizaciones que no pedí explícitamente en el servidor SQL.

Ya sea a través de una cascada o mediante el uso de desencadenantes. Tienden a morderte el culo en algún momento de la línea, ya sea al tratar de localizar un error o al diagnosticar problemas de rendimiento.

Donde los usaría es para garantizar consistencia por poco esfuerzo. Para obtener el mismo efecto, debería usar procedimientos almacenados.

Las claves externas son la mejor manera de garantizar la integridad referencial de una base de datos. Evitar cascadas debido a que es mágico es como escribir todo en ensamblador porque no confías en la magia detrás de los compiladores.

Lo que es malo es el uso incorrecto de claves externas, como crearlas al revés, por ejemplo.

El ejemplo de Juan Manuel es el ejemplo canónico, si usas el código, hay muchas más posibilidades de dejar documentos falsos en la base de datos que vendrán y te morderán.

Las actualizaciones en cascada son útiles, por ejemplo, cuando tiene referencias a los datos por algo que puede cambiar, por ejemplo, una clave principal de una tabla de usuarios es el nombre, la combinación del apellido. Luego desea que los cambios en esa combinación se propaguen a donde se les haga referencia.

@Aidan, esa claridad a la que se refiere tiene un alto costo, la posibilidad de dejar datos espurios en su base de datos, que no es pequeña . Para mí, generalmente es solo la falta de familiaridad con el DB y la incapacidad de encontrar qué FK están en su lugar antes de trabajar con el DB que fomenta ese miedo. O eso, o el uso indebido constante de la cascada, usándola donde las entidades no estaban relacionadas conceptualmente, o donde tienes que preservar el historial.

Las eliminaciones en cascada son extremadamente útiles cuando se implementan entidades lógicas de supertipo y subtipo en una base de datos física.

Cuando se utilizan tablas separadas de super-tipo y subtipo para implementar físicamente super-tipos / subtipos (en oposición a enrollar todos los atributos de subtipo en una sola tabla física de super-tipo), hay un único -una relación entre estas tablas y el problema se convierte en cómo mantener las claves primarias 100% sincronizadas entre estas tablas.

Cascade elimina puede ser una herramienta muy útil para:

1) Asegúrese de que eliminar un registro de super-tipo también borre el registro de subtipo único correspondiente.

2) Asegúrese de que cualquier eliminación de un registro de subtipo también elimine el registro de tipo súper. Esto se logra implementando un desencadenador de eliminación "en lugar de" en la tabla de subtipos que va y elimina el registro de supertipo correspondiente, que, a su vez, en cascada elimina el registro de subtipo.

El uso de eliminaciones en cascada de esta manera garantiza que nunca existan registros de súper tipo o subtipo huérfanos, independientemente de si elimina primero el registro de tipo súper o el registro de subtipo.

Lo he tenido en el pasado donde los DBA y / o la "Política de la compañía" han prohibido el uso de "On Delete Cascade" (y otros) simplemente por una mala experiencia en el pasado (en una ocasión, un tipo escribió tres desencadenantes que terminaron llamándose entre sí - 3 días para recuperarse dieron como resultado una prohibición total de los desencadenantes - debido a las acciones de un idjit).

Por supuesto, en algunos casos, se necesitarán Triggers en lugar de "On Delete cascade", cuando algunos datos secundarios necesitan ser preservados, pero en otros casos, es perfectamente válido para usar el método de cascada On Delete.

El Desarrollador debería poder tomar la decisión según el desarrollo y lo que dice la especificación. Una prohibición de la alfombra basada en una mala experiencia no debe ser el criterio. En Delete cascade capturará TODOS los elementos secundarios, un procedimiento de desencadenador / almacenamiento personalizado puede no funcionar si no está codificado correctamente.

El proceso de pensamiento "Nunca usar" es draconiano en el mejor de los casos. Se debe realizar una llamada de opinión todas y cada una de las veces, y los cambios deben realizarse a medida que cambia el modelo de negocio.

¿No es esto de lo que se trata el "Desarrollo"?

Nunca uso eliminaciones en cascada.

Si quiero eliminar algo de la base de datos, quiero decirle explícitamente a la base de datos lo que quiero eliminar.

Por supuesto, son una función disponible en la base de datos y puede haber ocasiones en las que está bien usarlas; por ejemplo, si tiene una tabla de ''orden'' y una tabla ''orderItem'', es posible que desee borrar los elementos cuando elimine un orden.

Me gusta la claridad que obtengo de hacerlo en código (o procedimiento almacenado) en lugar de que suceda ''magia''.

Por la misma razón, tampoco soy fanático de los desencadenantes.

Algo para notar es que si elimina una ''orden'' obtendrá un informe de ''1 fila afectada'' incluso si la eliminación en cascada ha eliminado 50 ''orderItem''s.

Resumen de lo que he visto hasta ahora:

Algunas personas no les gusta la cascada en absoluto.

Cascade Delete

Cascade Delete puede tener sentido cuando la semántica de la relación puede implicar una exclusiva "es parte de " la descripción. Por ejemplo, un registro de OrderLine es parte de su pedido principal, y OrderLines nunca se compartirá entre pedidos múltiples. Si la orden desapareciera, OrderLine también debería hacerlo, y una línea sin una orden sería un problema.
El ejemplo canónico para Cascade Delete es SomeObject y SomeObjectItems, donde no tiene sentido que exista un registro de elementos sin un registro principal correspondiente.
No debe usar Cascade Delete si conserva el historial o utiliza una "eliminación suave / lógica" en la que solo establece una columna de bit eliminado en 1 / verdadero.

Actualización en cascada

La actualización en cascada puede tener sentido cuando utiliza una clave real en lugar de una clave sustituta (columna de identidad / autoincremento) en las tablas.
El ejemplo canónico para Cascade Update es cuando tienes una clave externa mutable, como un nombre de usuario que se puede cambiar.
No debe usar Cascade Update con claves que sean columnas Identity / autoincrement.
Cascade Update se usa mejor junto con una restricción única.

Cuándo utilizar en cascada

Es posible que desee obtener una confirmación extra fuerte del usuario antes de permitir que una operación entre en cascada, pero depende de su aplicación.
La conexión en cascada puede generar problemas si configura incorrectamente las claves externas. Pero deberías estar bien si lo haces bien.
No es aconsejable usar la conexión en cascada antes de comprenderlo a fondo. Sin embargo, es una característica útil y, por lo tanto, vale la pena tomarse el tiempo para comprender.

Si está trabajando en un sistema con muchos módulos diferentes en diferentes versiones, puede ser muy útil, si los elementos eliminados en cascada son parte del titular de PK o son propiedad de este. De lo contrario, todos los módulos requerirían parches inmediatos para limpiar sus elementos dependientes antes de eliminar el propietario de PK, o la relación de clave externa se omitiría por completo, posiblemente dejando toneladas de basura en el sistema si la limpieza no se realiza correctamente.

Acabo de introducir la eliminación en cascada para una nueva tabla de intersección entre dos tablas ya existentes (la intersección para eliminar solamente), después de que se desaconsejara la eliminación de cascada durante bastante tiempo. Tampoco es tan malo si se pierde la información.

Sin embargo, es algo malo en las tablas de listas tipo enum: alguien borra la entrada 13 - amarilla de la tabla "colores", y todos los elementos amarillos en la base de datos se borran. Además, estos a veces se actualizan en una forma de eliminar todo insertar todo, lo que lleva a la integridad referencial totalmente omitida. Por supuesto que está mal, pero ¿cómo va a cambiar un software complejo que se ha estado ejecutando durante muchos años, con la introducción de una verdadera integridad referencial que corre el riesgo de sufrir efectos secundarios inesperados?

Otro problema es cuando los valores de las claves externas originales se mantendrán incluso después de que se haya eliminado la clave principal. Se puede crear una columna Tombstone y una opción ON DELETE SET NULL para el FK original, pero esto nuevamente requiere desencadenadores o código específico para mantener el valor clave redundante (excepto después de la eliminación PK).

Trabajo mucho con eliminaciones en cascada.

Se siente bien saber que quien trabaja en contra de la base de datos puede que nunca deje datos no deseados. Si las dependencias crecen, solo cambio las restricciones en el diagrama en Management Studio y no tengo que ajustar sp o dataacces.

Dicho esto, tengo 1 problema con las eliminaciones en cascada y las referencias circulares. Esto a menudo conduce a partes de la base de datos que no tienen eliminaciones en cascada.

Un ejemplo es cuando tienes dependencias entre entidades ... es decir: Documento -> Elementos de documento (cuando borras Documento, los Artículos de Documentos no tienen una razón para existir)

Una eliminación o actualización a S que elimina un valor de clave externa encontrado en algunas tuplas de R se puede manejar de una de estas tres formas:

Rechazo
Propagación
anulación.

La propagación se conoce como cascada.

Hay dos casos:

‣ Si se eliminó una tupla en S, elimine las tuplas R que se refieren a ella.

‣ Si se actualizó una tupla en S, actualice el valor en las R tuplas que se refieren a ella.

Una razón para colocar una eliminación en cascada (en lugar de hacerlo en el código) es mejorar el rendimiento.

Caso 1: con una eliminación en cascada

DELETE FROM table WHERE SomeDate < 7 years ago;

Caso 2: sin una cascada borrar

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

En segundo lugar, cuando agrega una tabla secundaria adicional con una eliminación en cascada, el código en el Caso 1 sigue funcionando.

Solo pondría una cascada donde la semántica de la relación es "parte de". De lo contrario, un idiota eliminará la mitad de su base de datos cuando lo haga:

DELETE FROM CURRENCY WHERE CurrencyCode = ''USD''

Use la eliminación de cascada donde desee que se elimine el registro con el FK si se eliminó su registro PK de referencia. En otras palabras, donde el registro no tiene sentido sin el registro de referencia.

Encuentro que la eliminación en cascada es útil para asegurar que las referencias muertas se eliminen de manera predeterminada en lugar de provocar excepciones nulas.

Yo, como todos los demás aquí, encuentro que las eliminaciones en cascada son realmente solo marginalmente útiles (en realidad no es tanto trabajo eliminar datos referenciados en otras tablas; si hay muchas tablas, simplemente automatiza esto con un script) pero realmente molestas cuando alguien accidentalmente elimina en cascada algunos datos importantes que son difíciles de restaurar.

El único caso en el que usaría es si los datos en la tabla de la tabla están altamente controlados (por ejemplo, permisos limitados) y solo se actualizan o eliminan a través de un proceso controlado (como una actualización de software) que se ha verificado.