formed - foreign key mysql ejemplo
Restricciones de clave foránea: cuándo usar ON UPDATE y ON DELETE (3)
Estoy diseñando el esquema de mi base de datos usando MySQL Workbench, lo cual es genial porque puedes hacer diagramas y los convierte: P
De todos modos, he decidido usar InnoDB debido a su soporte de clave externa. Sin embargo, una cosa que noté es que te permite establecer las opciones On Update y On Delete para las claves externas. ¿Alguien puede explicar dónde se podría usar "Restringir", "Cascada" y establecer nulo en un ejemplo simple?
Por ejemplo, supongamos que tengo una tabla de user que incluye un userID user . Y digo que tengo un mensaje de tabla de message que es un muchos-a-muchos que tiene 2 claves externas (que hacen referencia a la misma clave principal, userID de user en la tabla de user ). ¿Es útil establecer las opciones On Update y On Delete en este caso? Si es así, ¿cuál elijo? Si este no es un buen ejemplo, ¿podría darnos un buen ejemplo para ilustrar cómo podrían ser útiles?
Gracias
Además de la respuesta de @MarkR, una cosa a tener en cuenta sería que muchos frameworks PHP con ORM no reconocerían o usarían la configuración de base de datos avanzada (claves externas, eliminación en cascada, restricciones únicas), y esto podría provocar un comportamiento inesperado.
Por ejemplo, si borra un registro usando ORM, y DELETE CASCADE borrará los registros en las tablas relacionadas, el intento de ORM de eliminar estos registros relacionados (generalmente automáticos) dará como resultado un error.
No dude en poner restricciones en 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 que utilizan 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.
Tomaremos un ejemplo, una tabla de empresa con una tabla de usuarios que contiene personas de esta compañía
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 :
- ON RESTAURACIÓN DE ACTUALIZACIÓN : el valor predeterminado : si intenta actualizar un id_empresa en la tabla COMPAÑÍA, el motor rechazará la operación si un USUARIO al menos establece un enlace en esta empresa.
- EN ACTUALIZAR NO HAY ACCIÓN : igual que RESTRICT.
- ACTUALIZADO CASCADE : el mejor por lo general : si actualiza un company_id en una fila de la tabla COMPANY, el motor lo actualizará en consecuencia en todas las filas de USUARIO que hacen referencia a esta COMPAÑÍA (pero no activadores activados en la tabla USUARIO, advertencia). El motor rastreará los cambios por ti, 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 relacionado con eso en una actualización, pero puedo estar equivocado.
Y ahora en el lado ON DELETE :
- ON DELETE RESTRICT : el valor predeterminado : si intenta eliminar un ID de company_id en la tabla COMPANY, el motor rechazará la operación si un USUARIO al menos vincula en esta compañía, puede salvarle la vida.
- EN ELIMINAR NINGUNA ACCIÓN : igual que RESTRICT
- ON DELETE CASCADE : peligroso : si elimina una fila de la empresa en la tabla COMPANY, el motor eliminará también los USUARIOS relacionados. Esto es peligroso, pero se puede usar para realizar limpiezas automáticas en tablas secundarias (por lo que puede ser algo que desee, pero ciertamente no para un ejemplo de EMPRESA <-> USUARIO)
- ON DELETE SET NULL : handful : si elimina una fila de COMPANY, los USUARIOS relacionados tendrán automáticamente la relación con NULL. Si Null es su valor para usuarios sin compañía, esto puede ser un buen comportamiento, por ejemplo, tal vez necesite mantener a los usuarios en su aplicación, como autores de algún contenido, pero eliminar la empresa no es un problema para usted.
generalmente mi predeterminado es: ON DELETE RESTRICT ON UPDATE CASCADE . con algunos ON DELETE CASCADE para tablas de seguimiento (registros, no todos los registros, cosas así) y ON DELETE SET NULL cuando la tabla maestra es un ''atributo simple'' para la tabla que contiene la clave externa, como una tabla JOB para la tabla USER.
Editar
Ha pasado mucho tiempo desde que escribí eso. Ahora creo que debería agregar una advertencia importante. MySQL tiene una gran limitación documentada con cascadas. Las cascadas no disparan disparadores . Por lo tanto, si tiene suficiente confianza en ese motor para usar desencadenantes, debe evitar las limitaciones de cascadas.
Los activadores MySQL se activan solo para los cambios realizados en las tablas por las declaraciones SQL. No se activan por cambios en las vistas, ni por cambios a tablas hechas por API que no transmiten sentencias SQL al servidor MySQL
==> Vea 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 resuelva algún día. Las restricciones de clave externa son administradas por el almacenamiento InnoDb y los disparadores son administrados por el motor MySQL SQL. Ambos están separados. Innodb es el único almacenamiento con administración de restricciones, tal vez agregarán desencadenantes 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 aplicación de desencadenador deficiente y el soporte de restricciones de claves externas muy útiles. Y una vez que te acostumbres a la coherencia de la base de datos, te encantará PostgreSQL.
12/2017-Actualizando esta Edición sobre MySQL:
como lo expresó @IstiaqueAhmed en los comentarios, la situación ha cambiado en este tema. Siga el enlace y compruebe la situación real actualizada (que puede cambiar nuevamente en el futuro).
Tendrá que considerar esto en el contexto de la aplicación. En general, debe diseñar una aplicación, no una base de datos (la base de datos simplemente forma parte de la aplicación).
Considere cómo su aplicación debe responder a varios casos.
La acción predeterminada es restringir (es decir, no permitir) la operación, que normalmente es lo que desea, ya que evita errores de programación estúpidos. Sin embargo, en DELETE CASCADE también puede ser útil. Realmente depende de tu aplicación y de la forma en que pretendas eliminar objetos particulares.
Personalmente, utilizaría InnoDB porque no borra sus datos (cf. MyISAM, que sí lo hace), en lugar de porque tiene restricciones de FK.