ejemplos - foreign key sql server ejemplo

Cuándo usar "ACTUALIZAR CASCADE"

Question

Uso "ON DELETE CASCADE" con regularidad, pero nunca uso "ON UPDATE CASCADE" ya que no estoy seguro de en qué situación será útil.

Por el bien de la discusión, veamos algunos códigos.

CREATE TABLE parent (
    id INT NOT NULL AUTO_INCREMENT,
    PRIMARY KEY (id)
);

CREATE TABLE child (
    id INT NOT NULL AUTO_INCREMENT, parent_id INT,
    INDEX par_ind (parent_id),
    FOREIGN KEY (parent_id)
        REFERENCES parent(id)
        ON DELETE CASCADE
);

Para "ON DELETE CASCADE", si se elimina un padre con una id se eliminará automáticamente un registro en child con parent_id = parent.id . Esto no debería ser un problema.

1. ¿Esto significa que "ON UPDATE CASCADE" hará lo mismo cuando se actualice la id del padre?

2. Si (1) es verdadero, significa que no hay necesidad de usar "ON UPDATE CASCADE" si parent.id no es actualizable (o nunca se actualizará) como cuando es AUTO_INCREMENT o siempre se establece como TIMESTAMP . ¿Está bien?

3. Si (2) no es cierto, ¿en qué otro tipo de situación debemos usar "ON UPDATE CASCADE"?

4. ¿Qué pasa si (por alguna razón) actualizo que child.parent_id sea ​​algo que no existe, se eliminará automáticamente?

Bueno, lo sé, algunas de las preguntas anteriores se pueden probar mediante programación para comprender, pero también quiero saber si algo de esto depende del proveedor de la base de datos o no.

Por favor arroja algo de luz.

Answer 1

1. Sí, significa que, por ejemplo, si UPDATE parent SET id = 20 WHERE id = 10 todos los hijos parent_id''s de 10 también se actualizarán a 20

2. Si no actualiza el campo al que se refiere la clave externa, esta configuración no es necesaria

3. No se me ocurre ningún otro uso.

4. No puedes hacer eso porque la restricción de la clave externa fallaría.

Answer 2

¡Creo que ya has clavado los puntos!

Si sigue las mejores prácticas de diseño de la base de datos y su clave principal nunca es actualizable (lo cual creo que debería ser el caso de todos modos), entonces realmente nunca necesita la cláusula ON UPDATE CASCADE .

Zed destacó que si usa una clave natural (por ejemplo, un campo regular de la tabla de su base de datos) como su clave principal, entonces puede haber ciertas situaciones en las que necesite actualizar sus claves principales. Otro ejemplo reciente sería el ISBN (International Standard Book Numbers) que cambió de 10 a 13 dígitos + caracteres no hace mucho tiempo.

Este no es el caso si elige usar claves sustitutas (por ejemplo, generadas artificialmente por el sistema) como su clave principal (que sería mi elección preferida en todas las ocasiones, excepto en las más raras).

Así que al final: si su clave principal nunca cambia, nunca necesitará la cláusula ON UPDATE CASCADE .

Bagazo

Answer 3

Es cierto que si su clave principal es solo un valor de identidad incrementado automáticamente, no tendría un uso real para ON UPDATE CASCADE.

Sin embargo, digamos que su clave principal es un código de barras UPC de 10 dígitos y debido a la expansión, necesita cambiarlo por un código de barras UPC de 13 dígitos. En ese caso, ON UPDATE CASCADE le permitiría cambiar el valor de la clave principal y cualquier tabla que tenga referencias de clave externa al valor se cambiará en consecuencia.

En referencia al # 4, si cambia la ID del niño a algo que no existe en la tabla principal (y tiene integridad referencial), debería obtener un error de clave externa.

Answer 4

Es una excelente pregunta, tuve la misma pregunta ayer. Pensé en este problema, específicamente BÚSQUEDA si existía algo así como "ON UPDATE CASCADE" y, afortunadamente, los diseñadores de SQL también habían pensado en eso. Estoy de acuerdo con Ted.strauss, y también comenté el caso de Noran.

¿Cuándo lo usé? Como señaló Ted, cuando se tratan varias bases de datos a la vez, y la modificación en una de ellas, en una tabla, tiene cualquier tipo de reproducción en lo que Ted denomina "base de datos satelital", no puede conservarse con el original ID, y por cualquier motivo, tiene que crear uno nuevo, en caso de que no pueda actualizar los datos en el antiguo (por ejemplo, debido a los permisos, o en caso de que esté buscando rapidez en un caso que sea tan efímero que no merece el absoluto y absoluto respeto por las reglas totales de la normalización, simplemente porque será una utilidad de muy corta duración)

Entonces, estoy de acuerdo en dos puntos:

(A.) Sí, en muchas ocasiones un mejor diseño puede evitarlo; PERO

(B.) En casos de migraciones, replicación de bases de datos o resolución de emergencias, es una GRAN HERRAMIENTA que, afortunadamente, estaba allí cuando fui a buscar si existía.

Answer 5

Hace unos días tuve un problema con los desencadenantes, y he descubierto que ON UPDATE CASCADE puede ser útil. Echa un vistazo a este ejemplo (PostgreSQL):

CREATE TABLE club
(
    key SERIAL PRIMARY KEY,
    name TEXT UNIQUE
);

CREATE TABLE band
(
    key SERIAL PRIMARY KEY,
    name TEXT UNIQUE
);

CREATE TABLE concert
(
    key SERIAL PRIMARY KEY,
    club_name TEXT REFERENCES club(name) ON UPDATE CASCADE,
    band_name TEXT REFERENCES band(name) ON UPDATE CASCADE,
    concert_date DATE
);

En mi problema tuve que definir algunas operaciones adicionales (disparador) para actualizar la tabla de conciertos. Esas operaciones tuvieron que modificar club_name y band_name. No pude hacerlo, por referencia. No pude modificar el concierto y luego lidiar con las mesas del club y la banda. Tampoco podría hacerlo de la otra manera. ON UPDATE CASCADE fue la clave para resolver el problema.

Answer 6

Mi comentario es principalmente en referencia al punto 3: ¿en qué circunstancias es aplicable ACTUALIZAR CASCADE si asumimos que la clave principal no es actualizable? Aquí hay un caso.

Estoy tratando con un escenario de replicación en el que varias bases de datos satelitales deben fusionarse con un maestro. Cada satélite está generando datos en las mismas tablas, por lo que la combinación de las tablas con el maestro conduce a violaciones de la restricción de unicidad. Estoy tratando de usar ON UPDATE CASCADE como parte de una solución en la que re-incremento las claves durante cada combinación. ON UPDATE CASCADE debería simplificar este proceso al automatizar parte del proceso.