Restricciones de clave externa: Cuándo usar AL ACTUALIZAR y AL ELIMINAR

No dude en poner restricciones a la base de datos. Te asegurarás de tener una base de datos consistente, y esa es una de las buenas razones para usar una base de datos. Especialmente si tiene varias aplicaciones que lo solicitan (o solo una aplicación, pero con un modo directo y un modo por lotes utilizando diferentes fuentes).

Con MySQL no tiene restricciones avanzadas como las que tendría en PostgreSQL, pero al menos las restricciones de clave externa son bastante avanzadas.

Vamos a tomar un ejemplo, un tabla de empresa con una tabla de usuario que contiene personas de estas empresas

CREATE TABLE COMPANY (
     company_id INT NOT NULL,
     company_name VARCHAR(50),
     PRIMARY KEY (company_id)
) ENGINE=INNODB;

CREATE TABLE USER (
     user_id INT, 
     user_name VARCHAR(50), 
     company_id INT,
     INDEX company_id_idx (company_id),
     FOREIGN KEY (company_id) REFERENCES COMPANY (company_id) ON...
) ENGINE=INNODB;

Veamos la cláusula ON UPDATE:

• EN LA ACTUALIZACIÓN RESTRINGIR : el valor predeterminado : si intenta actualizar un company_id en la tabla COMPANY, el motor rechazará la operación si un USUARIO al menos enlaza con esta compañía.
• ON UPDATE NO ACTION: lo mismo que RESTRICT.
• EN CASCADA DE ACTUALIZACIÓN : el mejor generalmente: si usted actualizar un company_id en una fila de la tabla COMPANY el motor lo actualizará en consecuencia en todas las filas de USUARIOS que hacen referencia a esta EMPRESA (pero no hay disparadores activados en la tabla de USUARIOS, advertencia). El motor hará un seguimiento de los cambios para usted, es bueno.
• ON UPDATE SET NULL: si actualiza un company_id en una fila de la tabla COMPANY, el motor establecerá los usuarios relacionados company_id en NULL (debería estar disponible en el campo USER company_id). No puedo ver nada interesante que hacer con eso en una actualización, pero puede que me equivoque.

Y ahora en el lado DELETE :

• AL ELIMINAR RESTRINGIR : el valor predeterminado : si intenta eliminar un Id company_id en la tabla COMPANY, el motor rechazará la operación si un USUARIO al menos se enlaza en esta empresa, puede salvarle la vida.
• ON DELETE NO ACTION : same as RESTRICT
• ON DELETE CASCADE : dangerous: si elimina una fila de la compañía en la tabla el motor eliminará también a los usuarios relacionados. Esto es peligroso, pero se puede usar para hacer limpiezas automáticas en tablas secundarias (por lo que puede ser algo que desee, pero ciertamente no para un ejemplo de USUARIO de EMPRESA)
• ON DELETE SET NULL : handful : si elimina una fila de EMPRESA, los usuarios relacionados tendrán automáticamente la relación con NULL. Si Null es su valor para los usuarios sin compañía, esto puede ser un buen comportamiento, por ejemplo, tal vez necesite mantener la usuarios en su aplicación, como autores de algunos contenidos, pero la eliminación de la empresa no es un problema para usted.

Normalmente mi valor predeterminado es: AL ELIMINAR RESTRINGIR EN CASCADA DE ACTUALIZACIÓN. con algunos ON DELETE CASCADE para tablas de seguimiento (logs not not all logs., cosas así) y ON DELETE SET NULL cuando la tabla maestra es un 'atributo simple' para la tabla que contiene la clave foránea, como una tabla de TRABAJOS para la tabla de USUARIOS.

Editar

Ha pasado mucho tiempo desde que escribí eso. Ahora creo que Debo añadir una advertencia importante. MySQL tiene una gran limitación documentada con cascades. Las cascadas no disparan disparadores. Por lo tanto, si estaba lo suficientemente seguro en ese motor para usar disparadores, debería evitar las restricciones de cascadas.

• http://dev.mysql.com/doc/refman/5.6/en/triggers.html

Los disparadores de MySQL se activan solo para los cambios realizados en las tablas por instrucciones SQL. No se activan para cambios en vistas, ni por cambios en tablas hechas por APIs que no transmiten sentencias SQL al servidor MySQL

• http://dev.mysql.com/doc/refman/5.6/en/stored-program-restrictions.html#stored-routines-trigger-restrictions

==> Ver a continuación la última edición, las cosas se están moviendo en este dominio

Los desencadenadores no se activan mediante acciones de clave externa.

Y no creo que esto se arregle algún día. Las restricciones de clave externa son gestionadas por el El almacenamiento InnoDB y los disparadores son administrados por el motor SQL de MySQL. Ambos están separados. Innodb es el único almacenamiento con administración de restricciones, tal vez agreguen disparadores directamente en el motor de almacenamiento algún día, tal vez no.

Pero tengo mi propia opinión sobre qué elemento debe elegir entre la pobre implementación de trigger y el muy útil soporte de restricciones de claves foráneas. Y una vez que se acostumbre a la consistencia de la base de datos, le encantará PostgreSQL.

12/2017-Actualizando esta edición acerca de MySQL:

Como declaró @IstiaqueAhmed en los comentarios, la situación ha cambiado sobre este tema. Así que sigue el enlace y comprueba la situación actual real (que puede cambiar de nuevo en el futuro).