En este sistema, hemos de almacenar los productos, imágenes de productos (no puede ser de muchos la imagen de un producto), y una imagen predeterminada de 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 se puede ver, products_pictures.PRODUCT_ID -> products.ID y products.DEFAULT_PICTURE_ID -> products_pictures.ID, por lo que un ciclo de referencia. Está bien?

InformationsquelleAutor John Smith | 2012-05-04

6 Comentarios

  1. 43

    No, no está bien. Referencias circulares entre las mesas están sucias. Ver esto (década de edad) artículo: SQL Por el Diseño: La Referencia Circular

    Algunos DBMS puede manejar estos, y con especial atención, pero MySQL tendrá problemas.


    La primera opción es, como su diseño, para hacer uno de los dos FKs que aceptan valores null. Esto le permite resolver el huevo y la gallina problema (que mesa debo Insertar primero en?).

    Hay un problema con el código. Se permitirá que un producto tenga un defecto de la imagen donde esa imagen se hacer referencia a otro producto!

    Para no permitir este tipo de error, su FK restricción 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á un UNIQUE restricción/índice en la tabla products_pictures en (product_id, id) por encima de FK a ser definido y el trabajo correctamente.


    Otro enfoque es el de quitar la Default_Picture_ID en forma de columna de la product tabla y agregar un IsDefault BIT columna en la picture tabla. El problema con esta solución es cómo permitir sólo una imagen por producto que poco y que todos los demás tienen de ella. 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 tal característica.


    Un tercer enfoque, que permite incluso tener tanto FK columnas definidas como NOT NULL es el uso de aplazable restricciones. Esto funciona en PostgreSQL y creo que en Oracle. Marque esta pregunta y la respuesta por @Erwin: Complejo restricción de clave externa en SQLAlchemy (el Todas las columnas de clave not NULL Parte).

    Restricciones en MySQL no puede ser prorrogable.


    Un cuarto enfoque (que me parece más limpio) es quitar la Default_Picture_ID columna y agregar otra tabla. Ninguna trayectoria circular en el FK limitaciones y todas las columnas FK será 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á de un UNIQUE restricción/índice en la tabla products_pictures en (product_id, id) como en la solución 1.


    Para resumir, con MySQL, usted tiene dos opciones:

    • opción 1 (que acepta valores null FK columna) con la corrección de arriba para hacer cumplir la integridad correctamente

    • opción 4 (no se aceptan valores null FK columnas)

    • thnx
    • Este es un gran resumen (+1), pero no veo qué ventaja opción 4 tiene más opción 1. Con la opción 1, buggy el código de la aplicación puede crear datos no válidos por la creación de un producto y nunca dando un default_picture_id. Con la opción 4, buggy el código de la aplicación puede asimismo crear datos no válidos por la creación de un producto y nunca la creación de un product_default_picture fila para ello. Sin embargo, la selección de los productos junto con sus imágenes predeterminadas ahora requiere más se une, y el esquema es menos auto-documentado. ¿Qué beneficios hemos obtenido sobre el enfoque inicial para compensar la complejidad extra?
    • Uno de los más unirse no es un problema, ¿no? Una ventaja de la 4ª solución aparece cuando uno necesita almacenar más atributos por defecto de las imágenes. Esto podría funcionar en la 1ª solución así, con columnas que aceptan valores null, pero MySQL no tiene forma de imponer CHECK restricciones en ese caso (para asegurarse de que todos esos atributos adicionales son NULL o not NULL).
    • Y el 1 de solución tiene cierta complejidad de cuestiones. Uno tiene que ser cuidadoso en cómo la inserción de las filas (inserte primero un producto, a continuación, insertar sus imágenes, a continuación, actualizar el producto a punto de la imagen por defecto.) Lo mismo para cuando uno quiere cambiar la imagen predeterminada o quiere eliminar un producto.
    • Si con los «datos no válidos», que significa que las opciones (1 y 4) no exigir que todos los productos tienen una imagen por defecto, que son 100% a la derecha, no. Y ellos no pueden. Que no se había tratado en mi respuesta. Creo que esto sólo se puede hacer en la opción 3, con aplazable limitaciones y no acepta valores null columnas. En todas las otras opciones, que no se puede hacer con DDL sólo.
    • Sí, has entendido lo que quiero decir por «datos no válidos’ correctamente. Me doy cuenta de que no hay una solución clara a este en MySQL. No estoy seguro de la primera solución tiene la complejidad de los problemas que el cuarto no; he enumerado 3 pasos necesarios para crear un producto, sino el proceso en virtud de la cuarta solución es casi idéntica (la única diferencia es que en lugar de actualizar una fila del producto que estamos insertando un product_default_picture fila), que supongo que es mi punto principal. +1 a tu primer comentario, a pesar de querer extra NO NULOS en las columnas con información acerca de la relación.
    • Tal vez «complejidad» no es la mejor descripción. Aún así, la elimina sería desordenado, creo. Yo creo que por eso me dijo que el 4º es «más», para hacer insert, usted necesita 3 inserciones. para hacer un delete, usted necesita elimina (en 3 tablas o en 1 con cascada.) Apenas se parece más limpia a mí.
    • Así que, si elijo la 1 de enfoque, se puede esperar la ON DELETE CASCADE regla en FK_products_1 a trabajar o tengo que borrar la imagen por defecto de forma explícita antes de eliminar un producto?
    • Yo creo que depende del SGBD. Estamos hablando de MySQL?
    • Por el momento, estoy usando un ADO.NET desconectado conjunto de datos. No me di cuenta de que no puede depender de la DBMS.
    • De todos modos, si quieres cuando se elimina un producto de todas sus fotografías para ser eliminado, entonces FK_products_pictures_1 FK es lo que importa (y debe ser ON DELETE CASCADE).
    • El FK_products_1 importa a la hora de eliminar una imagen que es el predeterminado (y mi sugerencia 1 es tener que con ON DELETE RESTRICT).
    • Pero con SQL Server también puede utilizar la sugerencia 2. Es probablemente mucho más limpio.
    • ypercubeᵀᴹ de Hecho, eso es lo que quiero; sin embargo, cuando trato de borrar la fila Padre, me sale este error diciendo «No se puede eliminar esta fila debido a que las restricciones se aplican en relación FK_[DefaultChild]_[Parent], y eliminar esta fila se strand niño filas.» Yo no estoy usando SQL Server; sólo un conjunto de datos que se conserva como XML.
    • Lo siento, ahora quiero ver que pasa cuando intento eliminar el defecto hijo, no el padre, así que no tiene nada que ver con la On Delete Cascade regla.
    • Permítanos continuar esta discusión en el chat.
    • Con respecto a ADO.NET los conjuntos de datos con restricciones, mi conclusión es que asumen que las restricciones no son cíclicos, pero no se compruebe en tiempo de diseño o en tiempo de ejecución. Esto se traduce, a veces, de no ser capaz de eliminar un ciclo, eliminando la opción 1 y dejando sólo la opción 4.

  2. 4

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

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

    El único problema que vas a encontrar es que, cuando se inserta.
    ¿Cuál te insertar primero?

    Con esto, usted tendrá que hacer algo como:

    • Insertar producto con el valor predeterminado de la imagen
    • Insertar imagen(s) con el recién creado ID de producto
    • Actualización del producto para establecer el valor predeterminado de la imagen a uno que acaba de insertar.

    De nuevo, la eliminación no será divertido.

  4. 1

    De la otra tabla, usted sólo puede albergar ese campo sin la restricción foreign key.
    es útil en algunos casos en los que se desea procesar con los más pequeños de la tabla, pero se conectan a la mesa más grande con el resultado del proceso.

    Por ejemplo, si agrega un product_location tabla que tiene el país, el barrio, la ciudad, la dirección y la longitud y la latitud de la información. No podría ser un caso que se desee mostrar el producto dentro de un círculo en el mapa.

  5. 0

    Juan de lo que estás haciendo no es nada malo, pero el uso de PK FK realmente ayuda con la normalización de los datos mediante la eliminación de la repetición de los datos redundantes. Que tiene algo de fantástico ventajas de

    • Mejora de la integridad de los datos debido a la eliminación de la duplicación de los lugares de almacenamiento de los mismos datos
    • Reducción de contención de bloqueo y la mejora de varios usuarios de concurrencia
    • Archivos más pequeños
  6. -2

    que no es cíclica ref, que es pk fk

    • Quise decir, «a» se refiere a «B» y «B» se refiere a «Una», no que duele algo?
    • nada es romper aquí , yo creo que usted debe tomar un vistazo a los E F Codd las reglas y si es posible echar un vistazo a RDBMS

Dejar respuesta

Please enter your comment!
Please enter your name here