在 MySQL 中链接 2 个 ID

Question

我有这个表

CREATE TABLE IF NOT EXISTS `links` (
  `link_id` int(20) NOT NULL AUTO_INCREMENT,
  `item1_id` int(20) NOT NULL,
  `item2_id` int(20) NOT NULL,
  PRIMARY KEY (`link_id`),
  UNIQUE KEY `item_id` (`item1_id`,`item2_id`)
) ENGINE=InnoDB;

如何约束它，使the item_id只能在item1_id或item2_id中出现一次？

由于我希望一个项目只链接到另一个项目。。

Answer 1

我希望一个项目只链接到另一个项目

这意味着您不需要链接表。您只需要在Item表中有一个带有唯一约束的linkedItemId列。一旦Item2链接到Item1（Item1ID位于Item2行的linkedItemId中），其他任何列都无法链接到Item1。

此外，链接表不需要自己的代理密钥

编辑，注意MySQL允许在一个唯一索引中有多个NULL（不像SQL Server，在SQL Server中，你会使用过滤后的唯一索引来忽略NULL）

从MySQL 5.5 CREATE INDEX

对于所有引擎，UNIQUE索引允许包含NULL 的列有多个NULL值

Answer 2

我不认为INNODB可以强制执行这样的约束（或者MyIsam）。我的建议是创建一个存储过程来处理插入。首先检查您的自定义约束需求，然后在没有冲突的情况下照常插入。

Answer 3

。。。还有一个问题是，链接是否有明确的方向——即A-B与B-A不同吗？

我认为（您需要对此进行测试），您可以使用触发器处理这两种情况。然而，我不知道有任何方法可以通过MySQL的过程语言显式抛出错误。既然您已经指定了列应该为NOT NULL（没有默认值），并且假设NEW是可写的，那么。。。。

CREATE TRIGGER ins_link BEFORE INSERT on links
FOR EACH ROW
BEGIN
  IF (NEW.item1_id = NEW.item2_id) THEN
      NEW.item2_id=NULL; /* subsequent INSERT will fail */
  END IF
  /* if you want AB=BA.... */
  IF (NEW.item1_id > NEW.item2_id) THEN
     @tempvar=NEW.item1_id;
     NEW.item1_id=NEW.item2_id;
     NEW.item2_id=@tempvar;
  END IF;
END;

（您可能也需要更新前触发器，但代码的其余部分是相同的）。

Answer 4

您可以将UNIQUE KEY缩小为只有item1_id。这意味着该表定义了1:1关系，而不是1:n关系。此外，您可以删除auto_increment主键，这些"链接"表中不需要它：

CREATE TABLE IF NOT EXISTS links (
   item1_id int(20) NOT NULL,
   item2_id int(20) NOT NULL,
  PRIMARY KEY (item1_id),
  FOREIGN KEY (item1_id)                 --- I assume you have these 2
    REFERENCES item (item_id),           --- Foreign Keys, too
  FOREIGN KEY (item2_id)
    REFERENCES item (item_id)
) ENGINE=InnoDB;

这与@gbn的答案不同之处在于，这不允许使用null，并且不需要任何null来存储未链接的项。两种设计的工作原理几乎相同，只是在Insert/Delete/Update语句中做了一些小修改。

然而，在这两种设计中，我们都可以有像(1 -> 2)、(2 -> 3)、(3 -> 7)这样的连接对。如果符合要求的规格，两种设计都可以。

---

然而，如果我们只希望项目只出现在一个链接对中，在链接的两侧，则很难实现。

一种方法是确保links表中的所有插入都是通过插入(1, 2)和(2, 1)耦合或失败的过程完成的（对于必须处理2行的Delete/Update语句也是如此）。

其他更复杂的方法涉及触发器（或者奇异的结构，如索引视图，在MySQL中不可用）。

如果你想要一个规范化的设计，也有这种方法（复杂但没有触发器）：

CREATE TABLE IF NOT EXISTS link_help (
   item1_id int(20) NOT NULL,
   item2_id int(20) NOT NULL,
  PRIMARY KEY (item1_id),
  FOREIGN KEY (item1_id) 
    REFERENCES item (item_id),  
  FOREIGN KEY (item2_id)
    REFERENCES item (item_id),
  UNIQUE KEY (item1_id, item2_id)          --- this will be needed below
) ENGINE=InnoDB;
CREATE TABLE IF NOT EXISTS links (
   item1_id int(20) NOT NULL,
   item2_id int(20) NOT NULL,
  PRIMARY KEY (item1_id),
  FOREIGN KEY (item1_id, item2_id) 
    REFERENCES link_help (item1_id, item2_id), 
  FOREIGN KEY (item2_id, item1_id)               --- notice the different 
    REFERENCES link_help (item1_id, item2_id)    --- order here
) ENGINE=InnoDB;

现在，不能在links表中添加(1 -> 2)、(2 -> 3)和(3 -> 7)行。