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

问题描述

我正在将mysql数据库迁移到postgres，并在DDL中的下一个程序段中绊倒（注意：这是我从mysqldump获得的）：

  CREATE TABLE`catalog_property_value`（
`id` int（10）unsigned NOT NULL，
`property_id` int（10）unsigned NOT NULL，
`sort` int ）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`）参考`catalog_property`（`id`）ON DELETE CASCADE， 
 CONSTRAINT`FK_catalog_realty_property_value_link_realty` FOREIGN KEY（`realty_id`）参考`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`）参考`catalog_property_value`（`id`）ON DELETE CASCADE 
  / pre> 
 
 
那么这里有什么错？ 
解决方案
从手册中
 
 
 与SQL标准的偏差：引用
a非UNIQUE密钥的
 FOREIGN KEY约束不是标准SQL。 
它是一个InnoDB扩展标准的
 SQL。
 
 
所以看起来InnoDB允许非唯一索引作为外来参考的候选人。在其他地方，手册指出，只要引用的列首先列出并与主键的顺序相同，您可以引用引用的索引中的列子集。
 
 
 
因此，这个定义在InnoDB中是合法的，尽管它不是标准的SQL，但是让我至少对原始设计者的意图有些困惑。
 
 
 
 手册页面。
 
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;
Now, what I see here is that the only unique key in the first table is combination of (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!
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?
解决方案
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.
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.

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.

Manual page here.

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