Mysql外键由非唯一键 - 这怎么可能？

I was migrating mysql database to postgres and stumbled across the following block in DDL (Note: This is what I got from mysqldump):

我正在将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;

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

现在,我在这里看到的是第一个表中唯一的唯一键是(id,sort)的组合:

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

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

但是,第二个表只有id列的第一个引用,这不是唯一的!

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?

那么,我在这里弄错了什么?怎么可能?

4 个解决方案

#1

From the manual:

从手册:

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.

偏离SQL标准:引用非UNIQUE键的FOREIGN KEY约束不是标准SQL。它是标准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允许非唯一索引作为外键引用的候选者。在其他地方,手册指出您可以引用引用索引中的列子集,只要引用的列首先列出并且与主键的顺序相同。

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.

因此,这个定义在InnoDB中是合法的,虽然它不是标准的SQL,但至少让我对原始设计者的意图有点困惑。

Manual page here.

手册页面在这里。

#2

This weird behavior of FK's in innoDB is described in the manual.

手册中描述了FK在innoDB中的这种奇怪行为。

The handling of foreign key references to nonunique keys or keys that contain NULL values is not well defined for operations such as UPDATE or DELETE CASCADE. You are advised to use foreign keys that reference only UNIQUE and NOT NULL keys.

对于诸如UPDATE或DELETE CASCADE之类的操作,没有很好地定义对非唯一键或包含NULL值的键的外键引用的处理。建议您使用仅引用UNIQUE和NOT NULL键的外键。

PostgreSQL doesn't accept this construction, the foreign key has to point to a unique key.

PostgreSQL不接受这种结构,外键必须指向一个唯一的密钥。

#3

-1

This is perfectly legal according to wikipedia:

根据*,这是完全合法的:

The columns in the referencing table must be the primary key or other candidate key in the referenced table.

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

#4

-1

The most likely answer is that id really is unique in the catalog_propery_value table, but that the author declared the PK to be the superkey (id, sort) for reasons unknown, possibly having to do with indexing, rather than enforcing uniqueness.

最可能的答案是id在catalog_propery_value表中确实是唯一的,但是作者声明PK是超级密钥(id,sort),原因不明,可能与索引有关,而不是强制执行唯一性。

#1