En SQL, ¿está bien que dos tablas se refieran entre sí?

En este sistema, almacenamos productos, imágenes de productos (puede haber muchas imágenes para un producto) y una imagen predeterminada para un producto. La base de datos:

CREATE TABLE `products` ( `ID` int(10) unsigned NOT NULL AUTO_INCREMENT, `NAME` varchar(255) NOT NULL, `DESCRIPTION` text NOT NULL, `ENABLED` tinyint(1) NOT NULL DEFAULT '1', `DATEADDED` datetime NOT NULL, `DEFAULT_PICTURE_ID` int(10) unsigned DEFAULT NULL, PRIMARY KEY (`ID`), KEY `Index_2` (`DATEADDED`), KEY `FK_products_1` (`DEFAULT_PICTURE_ID`), CONSTRAINT `FK_products_1` FOREIGN KEY (`DEFAULT_PICTURE_ID`) REFERENCES `products_pictures` (`ID`) ON DELETE SET NULL ON UPDATE SET NULL ) ENGINE=InnoDB AUTO_INCREMENT=30 DEFAULT CHARSET=utf8; CREATE TABLE `products_pictures` ( `ID` int(10) unsigned NOT NULL AUTO_INCREMENT, `IMG_PATH` varchar(255) NOT NULL, `PRODUCT_ID` int(10) unsigned NOT NULL, PRIMARY KEY (`ID`), KEY `FK_products_pictures_1` (`PRODUCT_ID`), CONSTRAINT `FK_products_pictures_1` FOREIGN KEY (`PRODUCT_ID`) REFERENCES `products` (`ID`) ON DELETE CASCADE ) ENGINE=InnoDB AUTO_INCREMENT=20 DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC; 

como puede ver, products_pictures.PRODUCT_ID -> products.ID y products.DEFAULT_PICTURE_ID -> products_pictures.ID , por lo tanto, una referencia de ciclo. ¿Está bien?

No, no está bien. Las referencias circulares entre tablas son desordenadas. Ver este artículo (década de antigüedad): SQL By Design: The Circular Reference

Algunos DBMS pueden manejar estos y con especial cuidado, pero MySQL tendrá problemas.


La primera opción es, como su diseño, hacer que una de las dos FK sea nulable. Esto le permite resolver el problema del huevo y la gallina (¿a qué tabla debería primero inscribirme?).

Sin embargo, hay un problema con tu código. ¡Permitirá que un producto tenga una imagen predeterminada donde esa imagen hará referencia a otro producto!

Para no permitir tal error, su restricción FK debe ser:

 CONSTRAINT FK_products_1 FOREIGN KEY (id, default_picture_id) REFERENCES products_pictures (product_id, id) ON DELETE RESTRICT --- the SET NULL options would ON UPDATE RESTRICT --- lead to other issues 

Esto requerirá una restricción / índice UNIQUE en la tabla products_pictures on (product_id, id) para que se defina el FK anterior y funcione correctamente.


Otro enfoque es eliminar la columna Default_Picture_ID la tabla del product y agregar una columna IsDefault BIT en la tabla de picture . El problema con esta solución es cómo permitir que solo una imagen por producto tenga ese bit activado y que todos los demás lo tengan apagado. En SQL-Server (y creo que en Postgres) esto se puede hacer con un índice parcial:

 CREATE UNIQUE INDEX is_DefaultPicture ON products_pictures (Product_ID) WHERE IsDefault = 1 ; 

Pero MySQL no tiene esa característica.


Un tercer enfoque, que le permite incluso tener ambas columnas FK definidas como NOT NULL es usar restricciones diferibles. Esto funciona en PostgreSQL y creo en Oracle. Verifique esta pregunta y la respuesta de @Erwin: restricción de clave foránea compleja en SQLAlchemy (la columna Todas las columnas NO parte NULL ).

Las restricciones en MySQL no pueden ser diferibles.


Un cuarto enfoque (que me parece más limpio) es eliminar la columna Default_Picture_ID y agregar otra tabla. No hay una ruta circular en las restricciones FK y todas las columnas FK NOT NULL serán NOT NULL con esta solución:

 product_default_picture ---------------------- product_id NOT NULL default_picture_id NOT NULL PRIMARY KEY (product_id) FOREIGN KEY (product_id, default_picture_id) REFERENCES products_pictures (product_id, id) 

Esto también requerirá una restricción / índice UNIQUE en la tabla products_pictures on (product_id, id) como en la solución 1.


Para resumir, con MySQL tiene dos opciones:

  • opción 1 (una columna FK con nulo) con la corrección anterior para aplicar la integridad correctamente

  • opción 4 (no columnas NULL FK)

esto es sólo una sugerencia, pero si es posible crear una tabla de unión entre esta tabla puede ser útil para rastrear

 product_productcat_join ------------------------ ID(PK) ProductID(FK)- product table primary key PictureID(FK) - category table primary key 

El único problema con el que te encontrarás es cuando haces inserciones. ¿Cuál insertas primero?

Con esto, tendrás que hacer algo como:

  • Insertar producto con imagen predeterminada nula
  • Insertar imágenes con la nueva identificación del producto creado
  • Actualice el producto para establecer la imagen predeterminada a la que acaba de insertar.

De nuevo, eliminar no será divertido.

John lo que estás haciendo no es nada malo, pero usar PK-FK en realidad ayuda a normalizar tus datos eliminando los datos repetitivos redundantes. Que tiene algunas ventajas fantásticas de

  • Mejora de la integridad de los datos debido a la eliminación de ubicaciones de almacenamiento duplicadas para los mismos datos
  • Disminución de la contención de locking y mejora de la concurrencia de múltiples usuarios
  • Archivos más pequeños

eso no es una referencia cíclica, eso es pk-fk