MySQL非唯一键如何作为外键？这是否可行？

Question

我正在将mysql数据库迁移到postgres，并在DDL中遇到了以下块（注意：这是我从mysqldump获得的）：

CREATE TABLE `catalog_property_value` (
  `id` int(10) unsigned NOT NULL,
  `property_id` int(10) unsigned NOT NULL,
  `sort` int(10) unsigned NOT NULL,
  `value_number` decimal(15,5) DEFAULT NULL,
  `value_string` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`,`sort`),
  KEY `FK_catalog_property_value` (`property_id`),
  KEY `NewIndex1` (`id`),
  CONSTRAINT `FK_catalog_property_value` FOREIGN KEY (`property_id`) REFERENCES `catalog_property` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
SET character_set_client = @saved_cs_client;

CREATE TABLE `catalog_realty_property_value_link` (
  `realty_id` int(10) unsigned NOT NULL,
  `property_id` int(10) unsigned NOT NULL,
  `value_id` int(10) unsigned NOT NULL,
  `dt_is_denormalized` tinyint(1) unsigned NOT NULL,
  PRIMARY KEY (`realty_id`,`property_id`,`value_id`),
  KEY `FK_catalog_realty_property_value_link_property` (`property_id`),
  KEY `FK_catalog_realty_property_value_link_value` (`value_id`),
  CONSTRAINT `FK_catalog_realty_property_value_link_property` FOREIGN KEY (`property_id`) REFERENCES `catalog_property` (`id`) ON DELETE CASCADE,
  CONSTRAINT `FK_catalog_realty_property_value_link_realty` FOREIGN KEY (`realty_id`) REFERENCES `catalog_realty` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `FK_catalog_realty_property_value_link_value` FOREIGN KEY (`value_id`) REFERENCES `catalog_property_value` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

现在，我看到的是第一个表中唯一的关键字是(id, sort)组合：

PRIMARY KEY (`id`,`sort`),

然而，第二个表只通过id列引用第一个表，而该列并不唯一！

CONSTRAINT `FK_catalog_realty_property_value_link_value` FOREIGN KEY (`value_id`) REFERENCES `catalog_property_value` (`id`) ON DELETE CASCADE

那么，我在这里哪里出错了？这怎么可能呢？

- Maxim Sloyko

Answer 1

来自手册：

与 SQL 标准不同：引用非唯一键的外键约束不是标准 SQL，这是 InnoDB 对标准 SQL 的扩展。

因此，看起来 InnoDB 允许非唯一索引作为外键引用的候选项。 在手册的其他地方，它指出您可以引用引用索引中的一个子集，只要所引用的列在第一位且以与主键相同的顺序列出即可。

因此，在 InnoDB 中，此定义是合法的，尽管它不是标准的 SQL，并且至少让我对原始设计者的意图感到有些困惑。

手册页面在这里。

- Larry Lustig

---

如果您看到此页面：http://dev.mysql.com/doc/refman/5.0/en/innodb-foreign-key-constraints.html，则第一个要点提到外键必须引用一个索引，该索引首先使用索引列表中的列，并且按照约束指定的顺序排列。 - Larry Lustig

Answer 2

这里描述了innoDB中FK的奇怪行为，详见手册。

引用块：

当外键引用非唯一或包含NULL值的键时，在UPDATE或DELETE CASCADE等操作中处理不够明确。建议使用仅引用UNIQUE和NOT NULL键的外键。

PostgreSQL不接受此构造，外键必须指向唯一键。

- Frank Heikens

Answer 3

根据wikipedia，这是完全合法的：

引用表中的列必须是所引用表中的主键或其他候选键。

- soulmerge

---

候选键不就是唯一键吗？或者你是说多对多的关系可以用单个外键建模？我很困惑 :( - Maxim Sloyko

在这种情况下，引用表中的列根本不是任何关键字（而是更大关键字的一部分）。事实上，InnoDB似乎允许这种情况发生。但这并不是标准SQL。 - Larry Lustig

我不明白在维基对于外键或候选键的定义中，如何允许引用非唯一键。 - Tim Lehner

Answer 4

最有可能的答案是catalog_property_value表中id确实是唯一的，但作者将PK声明为超键（id，sort），原因未知，可能与索引有关，而不是强制唯一性。

- Walter Mitty

---

引擎怎么会知道id是唯一的？它要么被声明为唯一，要么没有被声明，而在这种情况下，它显然没有被声明为唯一。 - Larry Lustig