В SQL, нормально ли для двух таблиц ссылаться друг на друга?

В этой системе мы храним продукты, изображения продуктов (может быть много изображений для продукта) и образ по умолчанию для продукта. База данных:

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;

как вы можете видеть, products_pictures.PRODUCT_ID -> products.ID и products.DEFAULT_PICTURE_ID -> products_pictures.ID, поэтому ссылка цикла. Все в порядке?

Ответ 1

Нет, это не нормально. Циклические ссылки между таблицами беспорядочны. См. Эту статью (десятилетнюю давность): SQL By Design: Циркулярная ссылка

Некоторые СУБД могут справиться с ними и с особой осторожностью, но у MySQL возникнут проблемы.

Первый выбор - как ваш дизайн - сделать одно из двух FKs допустимым. Это позволяет решить проблему с курицей и яйцом (в какую таблицу я должен сначала вставить?).

Есть проблема, хотя с вашим кодом. Это позволит продукту иметь изображение по умолчанию, где это изображение будет ссылаться на другой продукт!

Чтобы запретить такую ошибку, ваше ограничение FK должно быть:

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

Для приведенного выше FK для определения и правильной работы требуется таблица ограничений UNIQUE в таблице products_pictures на (product_id, id).

Другой подход заключается в удалении столбца Default_Picture_ID формы таблицы product и добавлении столбца IsDefault BIT в таблице picture. Проблема с этим решением заключается в том, как разрешить только одному изображению на продукт иметь этот бит и все остальные, чтобы отключить его. В SQL-Server (и я думаю, что в Postgres) это можно сделать с помощью частичного индекса:

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

Но у MySQL нет такой возможности.

Третий подход, который позволяет даже иметь оба столбца FK, определенные как NOT NULL, использовать отложенные ограничения. Это работает в PostgreSQL, и я думаю, что в Oracle. Проверьте этот вопрос и ответ @Erwin: Сложное ограничение внешнего ключа в SQLAlchemy (Все ключевые столбцы NOT NULL).

Ограничения в MySQL не могут быть отложенными.

Четвертый подход (который я считаю самым чистым) заключается в удалении столбца Default_Picture_ID и добавлении другой таблицы. Никакой круговой путь в ограничениях FK, и все столбцы FK будут NOT NULL с этим решением:

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)

Это также потребует UNIQUE ограничение/индекс в таблице products_pictures на (product_id, id), как в решении 1.

Подводя итог, с MySQL у вас есть два варианта:

вариант 1 (нулевой столбец FK) с исправлением выше для правильной реализации целостности
вариант 4 (без нулевых столбцов FK)

Ответ 2

Это просто предложение, но, если возможно, создать одну таблицу соединений между этой таблицей может быть полезно для отслеживания

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

Ответ 3

Единственная проблема, с которой вы столкнетесь, - это когда вы вставляете. Какой из них вы вставляете первым?

При этом вам нужно будет сделать что-то вроде:

Вставить продукт с нулевым изображением по умолчанию
Вставьте снимок с вновь созданным идентификатором продукта
Обновите продукт, чтобы установить изображение по умолчанию на тот, который вы только что вставили.

Снова, удаление не будет забавным.

Ответ 4

Джон, что вы делаете, ничего плохого, но использование PK-FK действительно помогает с нормализацией ваших данных, удаляя избыточные повторяющиеся данные. Что имеет некоторые фантастические преимущества от

Улучшена целостность данных из-за устранения дубликатов мест хранения для тех же данных
Сокращение блокировки и улучшенный многопользовательский concurrency
Меньшие файлы

Ответ 5

который не является циклическим ref, то есть pk-fk