在SQL中，两个表相互引用是否可以？

一尘不染

在SQL中，两个表相互引用是否可以？

mysql

在此系统中，我们存储产品，产品图像（产品可能有很多图像）和产品的默认图像。数据库：

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，是循环参考。可以吗

阅读 590

2020-05-17

共1个答案

一尘不染

不，那不行。表之间的循环引用是混乱的。请参阅这篇（已有十年的历史）文章：SQL By
Design：循环参考

一些DBMS可以特别小心地处理这些问题，但是MySQL会遇到问题。

作为您的设计，第一选择是使两个FK之一为可空。这使您能够解决“鸡与蛋”问题（我应该首先插入哪个表？）。

但是您的代码有问题。它将允许产品具有默认图片，该图片将引用其他产品！

为避免此类错误，您的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

这将要求UNIQUE在表products_pictureson上有一个约束/索引，(product_id, id)以定义上述FK并正常工作。

另一种方法是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列）

2020-05-17