¿Cuándo y por qué usar la conexión en cascada en SQL Server?

Las claves foráneas son la mejor manera de garantizar la integridad referencial de una base de datos. Evitar cascadas debido a ser magia es como escribir todo en ensamblado porque no confías en la magia detrás de los compiladores.

Lo malo es el mal uso de claves foráneas, como crearlas al revés, por ejemplo.

El ejemplo de Juan Manuel es el ejemplo canónico, si usas código hay muchas más posibilidades de dejar documentos espurios 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, digamos que una clave primaria de una tabla de usuarios es la combinación name,lastname. Entonces desea que los cambios en esa combinación se propaguen a donde quiera que se haga referencia a ellos.

@Aidan, esa claridad a la que se refiere tiene un alto costo, la posibilidad de dejar datos espurios en su base de datos, que es no pequeña. Para mí, por lo general es solo falta de familiaridad con la DB y incapacidad para encontrar qué FKs están en su lugar antes de trabajar con el DB que fomentan ese miedo. O eso, o el uso indebido constante de cascade, usándolo donde las entidades no estaban conceptualmente relacionadas, o donde hay que preservar la historia.

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 sacar.

Por supuesto, son una función disponible en la base de datos y puede haber ocasiones en las que esté bien usarlos, por ejemplo, si tiene una tabla 'order' y una tabla 'OrderItem', es posible que desee borrar los elementos cuando elimine un pedido.

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

Por la misma razón no soy un fan de los disparadores tampoco.

Algo a tener en cuenta es que si elimina un 'orden ' obtendrá' 1 fila afectada 'informe de nuevo, incluso si la eliminación en cascada ha eliminado 50' OrderItem.

Trabajo mucho con eliminaciones en cascada.

Se siente bien saber que quien trabaja contra la base de datos nunca puede dejar ningún dato no deseado. Si las dependencias crecen, simplemente cambio las restricciones en el diagrama en Management Studio y no tengo que modificar sp o dataacces.

Dicho esto, tengo 1 problema con las eliminaciones en cascada y eso es 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... ie: Document - > DocumentItems (cuando se elimina el documento, DocumentItems no tienen una razón para existir)

Hago mucho trabajo en bases de datos y rara vez encuentro útiles las eliminaciones en cascada. La única vez que los he utilizado de manera efectiva es en una base de datos de informes que se actualiza por un trabajo nocturno. Me aseguro de que los datos modificados se importen correctamente eliminando cualquier registro de nivel superior que haya cambiado desde la última importación, luego reimportar los registros modificados y todo lo que se relacione con ellos. Me ahorra tener que escribir muchas eliminaciones complicadas que se ven desde la parte inferior hasta la parte superior de mi base de datos.

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

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

Use cascade delete donde desea que se elimine el registro con el FK si se eliminó su registro PK de referencia. En otras palabras, cuando el registro carece de sentido sin el registro de referencia.

Encuentro que cascade delete es útil para asegurar que las referencias muertas se eliminen por defecto en lugar de causar excepciones nulas.

ON Delete Cascade:

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

Si en cascade delete no se usa, entonces se generará un error para referential integrity.

EN Cascada de actualización:

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

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

Caso 1: Con una cascada suprímase

 DELETE FROM table WHERE SomeDate < 7 years ago;

Caso 2: Sin cascada suprímase

 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 en una cascada donde la semántica de la relación es "parte de". De lo contrario algún idiota borrará la mitad de tu base de datos cuando lo hagas:

DELETE FROM CURRENCY WHERE CurrencyCode = 'USD'

Trato de evitar eliminaciones o actualizaciones que no solicité explícitamente en SQL server.

Ya sea a través de la cascada o mediante el uso de disparadores. Tienden a morderte en el culo en algún momento, ya sea cuando intentas rastrear un error o cuando diagnosticas problemas de rendimiento.

Donde los usaría es para garantizar la consistencia por no mucho esfuerzo. Para obtener el mismo efecto, tendría que usar procedimientos almacenados.

Yo, como todos los demás aquí, encuentro que las eliminaciones en cascada son realmente solo marginalmente útiles (en realidad no es mucho trabajo eliminar los datos referenciados en otras tablas if si hay muchas tablas, simplemente automatiza esto con un script), pero realmente molesto 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 de la tabla están altamente controlados (por ejemplo, permisos limitados) y solo se actualizan o eliminado a través de un proceso controlado (como una actualización de software) que ha sido verificado.

He oído hablar de DBA y/o "Política de la compañía" que prohíben el uso de "On Delete Cascade" (y otros) simplemente debido a malas experiencias en el pasado. En un caso, un tipo escribió tres disparadores que terminaron llamándose el uno al otro. Tres días para recuperarse resultaron en una prohibición total de los disparadores, todo debido a las acciones de un idjit.

Por supuesto, a veces se necesitan disparadores en lugar de "On Delete cascade", como cuando algunos datos secundarios deben conservarse. Pero en otros casos, es perfectamente válido para usar el método On Delete cascade. Una ventaja clave de "On Delete cascade" es que captura TODOS los hijos; un procedimiento de activación/almacenamiento escrito personalizado puede no hacerlo si no está codificado correctamente.

Creo que al Desarrollador se le debe permitir tomar la decisión basada en lo que es el desarrollo y lo que dice la especificación. Una prohibición de alfombras basada en una mala experiencia no debería ser el criterio; el proceso de pensamiento de "Nunca usar" es draconiano en el mejor de los casos. Un juicio debe hacerse cada tiempo, y los cambios realizados a medida que cambia el modelo de negocio.

¿No se trata de esto el desarrollo?

Una eliminación o actualización a S que elimina un valor de clave foránea encontrado en algunas tuplas de R se puede manejar de una de tres maneras:

1. Rechazo
2. Propagación
3. anulación.

La propagación se conoce como cascada.

Hay dos casos:

If Si se eliminó una tupla en S, elimine las tuplas R que se referían a ella.

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

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 de / propiedad del titular de PK. 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 cascade delete para un nuevo tabla de intersección entre dos tablas ya existentes (la intersección para eliminar solamente), después de que cascade delete se había desaconsejado desde hace bastante tiempo. Tampoco es tan malo si los datos se pierden.

Es, sin embargo, algo malo en las tablas de lista tipo enumeración: alguien elimina la entrada 13-amarillo de la tabla "colores", y todos los elementos amarillos en la base de datos se eliminan. Además, estos a veces se actualizan de una manera delete-all-insert-all, lo que lleva a la integridad referencial totalmente omitida. Por supuesto está mal, pero ¿cómo va a cambiar un software complejo que ha estado funcionando durante muchos años, con la introducción de la verdadera integridad referencial en riesgo de efectos secundarios inesperados?

Otro problema es cuando los valores de la clave foránea original deben mantenerse incluso después de que se haya eliminado la clave primaria. Se puede crear una columna tombstone y una opción ON DELETE SET NULL para el FK original, pero esto nuevamente requiere disparadores o código específico para mantener el redundante (excepto después de la eliminación de PK) valor clave.

Las eliminaciones en cascada son extremadamente útiles cuando se implementan entidades de supertipos y subtipos lógicos en una base de datos física.

Cuando se usan tablas de supertipos y subtipos separadas para implementar físicamente supertipos/subtipos (en lugar de agrupar todos los atributos de subtipos en una sola tabla de supertipos físicos), hay una relación uno a uno entre estas tablas y el problema se convierte en cómo mantener las claves primarias 100% sincronizadas entre tabla.

Las eliminaciones en cascada pueden ser una herramienta muy útil para:

1) Asegúrese de que al eliminar un registro de supertipo también se borra el registro de subtipo individual correspondiente.

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

Usando cascada elimina de esta manera garantiza que nunca existan registros de supertipos o subtipos huérfanos, independientemente de si elimina primero el registro de supertipos o el registro de subtipos.