非唯一键的Mysql外键——这怎么可能? [英] Mysql foreign key by non unique key -- how is that possible?

问题描述

我正在将 mysql 数据库迁移到 postgres 并偶然发现 DDL 中的以下块(注意:这是我从 mysqldump 得到的):

I was migrating mysql database to postgres and stumbled across the following block in DDL (Note: This is what I got from 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) 的组合:

Now, what I see here is that the only unique key in the first table is combination of (id, sort):

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

但是，第二个表仅通过 id 列引用了第一个表，这不是唯一的！

however, the second table has a reference to the first on by only id column, which is not unique!

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

那么，我在这里做错了什么?这怎么可能?

So, what did I get wrong here? How is that possible?

推荐答案

来自手册:

偏离 SQL 标准:A引用的 FOREIGN KEY 约束非唯一键不是标准 SQL.它是标准的 InnoDB 扩展SQL.

Deviation from SQL standards: A FOREIGN KEY constraint that references a non-UNIQUE key is not standard SQL. It is an InnoDB extension to standard SQL.

所以看起来 InnoDB 允许非唯一索引作为外键引用的候选者.手册的其他地方指出，您可以在引用的索引中引用列的子集，只要引用的列首先列出并且与主键的顺序相同.

So it looks like InnoDB allows non-unique indexes as candidates for foreign key references. Elsewhere the manual states that you can reference a subset of columns in the referenced index as long as the referenced columns are listed first and in the same order as the primary key.

因此，这个定义在 InnoDB 中是合法的，尽管它不是标准 SQL，至少让我对最初设计者的意图有点困惑.

Therefore, this definition is legal in InnoDB, although it's not standard SQL and leaves me, at least, a little confused as to the original designer's intentions.

此处为手册页.

这篇关于非唯一键的Mysql外键——这怎么可能?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！