sql - tabla - ¿Cómo funcionan las opciones de la clave externa de Postgres ''en actualización'' y ''en eliminar''?

llave foranea sql

Question

¿Alguien puede dar una explicación clara / ejemplo de lo que hacen estas funciones, y cuándo es apropiado usarlas?

Answer 1

Lo que Daok dice es verdad ... puede ser bastante conveniente. Por otro lado, que las cosas sucedan automágicamente en la base de datos puede ser un problema real, especialmente cuando se trata de eliminar datos. Es posible que en el futuro alguien cuente con el hecho de que los FK normalmente evitan la eliminación de los padres cuando hay niños y no se dan cuenta de que el uso de On Delete Cascade no solo evita la eliminación, sino que hace enormes cantidades de datos en docenas de otras tablas desaparecen gracias a una cascada de eliminaciones en cascada.

@ Comentario de Arthur.

Cuantas más cosas "ocultas" suceden en la base de datos, menos probable es que alguien tenga un buen manejo de lo que está sucediendo. Los desencadenantes (y esto es esencialmente un desencadenante) pueden hacer que mi simple acción de eliminar una fila tenga consecuencias de amplio alcance en toda mi base de datos. Emití una instrucción Delete y 17 tablas se ven afectadas con cascadas de desencadenantes y restricciones, y nada de esto es inmediatamente aparente para el emisor del comando. OTOH, si coloco la eliminación del padre y todos sus hijos en un procedimiento, entonces es muy fácil y claro que cualquiera pueda ver EXACTAMENTE lo que sucederá cuando emita el comando.

No tiene absolutamente nada que ver con lo bien que diseño una base de datos. Tiene todo que ver con los problemas operacionales introducidos por los factores desencadenantes.

Answer 2

Tengo una base de datos PostGreSQL y uso On Delete cuando tengo un usuario que elimino de la base de datos y necesito eliminar su información de otra tabla. De esta forma tengo que hacer solo 1 eliminación y FK que tiene activado eliminar eliminará la información de otra tabla.

Puedes hacer lo mismo con ON Update. Si actualiza la tabla y el campo tiene un FK con En Actualización, si se realiza un cambio en el FK, se lo notará en la tabla FK.

Answer 3

En lugar de escribir el método para hacer todo el trabajo, de la eliminación en cascada o de la actualización en cascada, simplemente podría escribir un mensaje de advertencia. Mucho más fácil que reinventar la rueda y deja en claro al cliente (y a los nuevos desarrolladores que recogen el código)

Answer 4

Directamente del manual ...

Sabemos que las claves externas no permiten la creación de pedidos que no se relacionan con ningún producto. Pero, ¿qué pasa si un producto se elimina después de que se crea un pedido que lo hace referencia? SQL le permite manejar eso también. Intuitivamente, tenemos algunas opciones:

No permitir la eliminación de un producto al que se hace referencia

Eliminar los pedidos también

¿Algo más?

CREATE TABLE order_items (
 product_no integer REFERENCES products ON DELETE RESTRICT,
 order_id integer REFERENCES orders ON DELETE CASCADE,
 quantity integer,
 PRIMARY KEY (product_no, order_id)
);

Las eliminaciones en cascada y en cascada son las dos opciones más comunes. RESTRICT evita la eliminación de una fila a la que se hace referencia. NO ACTION significa que si todavía existen filas de referencia cuando se marca la restricción, se genera un error; este es el comportamiento predeterminado si no especificas nada. (La diferencia esencial entre estas dos opciones es que NO ACTION permite que la verificación se posponga hasta más adelante en la transacción, mientras que RESTRICT no). CASCADE especifica que cuando se borra una fila referenciada, las filas que hacen referencia a ella deben borrarse automáticamente también. Hay otras dos opciones: SET NULL y SET DEFAULT. Esto provoca que las columnas de referencia se establezcan en nulos o valores predeterminados, respectivamente, cuando se borra la fila referenciada. Tenga en cuenta que estos no lo excusan de observar ninguna restricción. Por ejemplo, si una acción especifica SET DEFAULT pero el valor predeterminado no satisfaría la clave foránea, la operación fallará.

Análogamente a ON DELETE también hay ON UPDATE que se invoca cuando se cambia (se actualiza) una columna referenciada. Las posibles acciones son las mismas.

editar: es posible que desee echar un vistazo a esta pregunta relacionada: ¿ Cuándo / por qué utilizar Cascading en SQL Server? . Los conceptos detrás de las preguntas / respuestas son los mismos.