MySQL 是否可以将一列引用为多个外键？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15547276/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Is it possible to reference one column as multiple foreign keys?
提问by insict
I have few tables, and I want to reference one column from PDF table to multiple other tables.
我有几个表，我想将 PDF 表中的一列引用到多个其他表。
for example if PDF table selectoutput looks like this:
例如，如果 PDF 表格select输出如下所示：
ITEM_TYPE ITEM_ID QUANTITY
1 23 3
2 12 1
it tells me:
它告诉我：
PDF have 3 Car Wheel Product, and 1 Car Template Header above;
PDF有3个车轮产品，上面有1个汽车模板标题；
I wrote SQL code, but does not work properly:
我写了 SQL 代码，但不能正常工作：
CREATE TABLE `pdf_created` (
`id` INT(10) UNSIGNED NOT NULL UNIQUE AUTO_INCREMENT,
`pdf_id` INT(10) NOT NULL,
`item_type` INT(3) UNSIGNED NOT NULL,
`item_id` INT(10) UNSIGNED NOT NULL,
`quantity` INT(3) NOT NULL,
PRIMARY KEY (`id`),
KEY `FK_pdf_id` (`pdf_id`),
CONSTRAINT `FK_pdf_id` FOREIGN KEY (`pdf_id`) REFERENCES `pdf` (`id`),
KEY `FK_item_type` (`item_type`),
CONSTRAINT `FK_item_type` FOREIGN KEY (`item_type`) REFERENCES `item` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
KEY `FK_item_id` (`item_id`),
CONSTRAINT `FK_item_id` FOREIGN KEY (`item_id`) REFERENCES `product` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `FK_item_id` FOREIGN KEY (`item_id`) REFERENCES `service` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `FK_item_id` FOREIGN KEY (`item_id`) REFERENCES `header` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE `header` (
`id` INT(10) UNSIGNED NOT NULL UNIQUE AUTO_INCREMENT,
`title` VARCHAR(255),
`desc` VARCHAR(65535),
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE `service` (
`id` INT(10) UNSIGNED NOT NULL UNIQUE AUTO_INCREMENT,
`desc` VARCHAR(65535) NOT NULL,
`price` DECIMAL(5,2) NOT NULL,
`active` INT(1) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE `product` (
`id` INT(10) UNSIGNED NOT NULL UNIQUE AUTO_INCREMENT,
`category_id` INT(3) UNSIGNED NOT NULL,
`symbol` VARCHAR(255),
`desc` VARCHAR(65535),
`price` DECIMAL(5,2) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
it is possible to create it?
有可能创建它吗？
回答by Marlin Pierce
No.
不。
That is, you cannot create a foreign key constraint this way. You can however, use a foreign key without a foreign key constraint.
也就是说，您不能以这种方式创建外键约束。但是，您可以使用没有外键约束的外键。
All a foreign key is, is the value of another table's (or another record in the same table) primary key, which can be used in joins. In fact, you could reference fields other than the primary key, if all you need is to use the value for joins.
所有外键都是另一个表（或同一表中的另一条记录）主键的值，可用于连接。事实上，如果您只需要使用连接的值，您可以引用主键以外的字段。
However, a foreign key constraint tells the database to enforce the rule that for every foreign key value in a table, the referenced table has a record with that as it's primary key. Enforcing that every foreign key in the PDF table had a primary key IN ALL FOUR TABLESwon't work for you. So go ahead and use the field to reference other records, but simply do not create any foreign key constraint.
但是，外键约束告诉数据库强制执行这样的规则，即对于表中的每个外键值，被引用的表都有一个记录作为它的主键。强制 PDF 表中的每个外键在所有四个表中都有一个主键对您不起作用。所以继续使用该字段来引用其他记录，但不要创建任何外键约束。
回答by Browny Lin
The problem you met is called Polymorphic Associations
你遇到的问题叫做 Polymorphic Associations
Please refer this question: MySQL - Conditional Foreign Key Constraints
请参考这个问题：MySQL - 条件外键约束
回答by Dan Bracuk
It should be possible. One potential problem is that your three foreign key constraints have the same name.
应该是可以的。一个潜在的问题是您的三个外键约束具有相同的名称。
回答by Marlin Pierce
No, one foreign key field is meant to reference one table.
不，一个外键字段旨在引用一张表。
If you did have the FK constraints as you describe, a item_id field would reference the same primary key value in all three tables. It would be very likely that the desired primary key in the three different tables would have different primary keys.
如果您确实有您描述的 FK 约束，则 item_id 字段将在所有三个表中引用相同的主键值。三个不同表中所需的主键很可能具有不同的主键。
What you want is for one record (row) to reference records in tables Product, Header, and Service. The way to do that is to use three different fields, one for each foreign key.
您想要的是一条记录（行）来引用表 Product、Header 和 Service 中的记录。这样做的方法是使用三个不同的字段，一个用于每个外键。
I also notice that the Item table has the three foreign keysneeded. You could have the PDF table have one field which references Item, and the record in Item references the three other tables.
我还注意到 Item 表具有所需的三个外键。您可以让 PDF 表有一个字段引用 Item，Item 中的记录引用其他三个表。
回答by Rahul Pandey
ya its possible foreign key constraint name should be different like this and primary key and foreign key table column should have same data type like this..
ya 它可能的外键约束名称应该像这样不同，并且主键和外键表列应该具有相同的数据类型。
CREATE TABLE `neo_address_t` (
`address_id` varchar(8) NOT NULL,
`address_line_1` varchar(45) NOT NULL,
`address_line_2` varchar(45) NOT NULL,
`address_line_3` varchar(45) NOT NULL,
`address_city` varchar(45) NOT NULL,
`address_zipcode` varchar(45) NOT NULL,
`address_state` varchar(45) NOT NULL DEFAULT 'Karnataka',
`address_country` varchar(45) NOT NULL DEFAULT 'INDIA',
`created_by` varchar(8) DEFAULT NULL,
`created_on` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`last_modified_by` varchar(8) DEFAULT NULL,
`last_modified_date` timestamp NULL DEFAULT '0000-00-00 00:00:00',
`Refer_ID` int(11) DEFAULT NULL,
`a_id` varchar(255) DEFAULT NULL,
`referenceid` varchar(255) DEFAULT NULL,
PRIMARY KEY (`address_id`),
KEY `hospital_ID_FK_idx` (`Refer_ID`),
CONSTRAINT `Patient_ID_FK` FOREIGN KEY (`Refer_ID`) REFERENCES `neo_patient_t` (`patient_ID`) ON DELETE NO ACTION ON UPDATE NO ACTION,
CONSTRAINT `hospital_ID_FK` FOREIGN KEY (`Refer_ID`) REFERENCES `neo_hospital_t` (`hospital_id`) ON DELETE NO ACTION ON UPDATE CASCADE,
CONSTRAINT `staff_ID_FK` FOREIGN KEY (`Refer_ID`) REFERENCES `neo_staff_t` (`staff_ID`) ON DELETE NO ACTION ON UPDATE NO ACTION
)
ENGINE=InnoDB DEFAULT CHARSET=utf8$$
回答by acsinha
Theoretically you can not enforce multiple foreign key on single column. Alternatively you can enforce this using procedures where you validate the input which exists in multiple table and do the needful operation. Be careful that all the operation on that particular table should be done by the procedures which validates the required condition else it would leads in violation of integrity.
理论上你不能在单列上强制执行多个外键。或者，您可以使用验证存在于多个表中的输入并执行所需操作的过程来强制执行此操作。请注意，该特定表上的所有操作都应由验证所需条件的过程完成，否则会导致违反完整性。
回答by René Link
Yes, it's possible. Even if it seems a bit strange.
是的，这是可能的。即使看起来有点奇怪。
First I would like to show a screenshot of my MySQL Workbench to testify that it works.
首先，我想展示我的 MySQL Workbench 的屏幕截图以证明它有效。
...and for those that say "Maybe you forgot to apply the changes"... Here is the screenshot of the schema browser:
...对于那些说“也许您忘记应用更改”的人...这是架构浏览器的屏幕截图：
and finally the exported dump with some example data:
最后是带有一些示例数据的导出转储：
-- MySQL dump 10.13 Distrib 5.7.12, for Win64 (x86_64)
--
-- Host: localhost Database: multiple_foreign_keys
-- ------------------------------------------------------
-- Server version 5.7.17-log
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
--
-- Table structure for table `header`
--
DROP TABLE IF EXISTS `header`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `header` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`title` varchar(255) DEFAULT NULL,
`desc` varchar(255) DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `id` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=43 DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Dumping data for table `header`
--
LOCK TABLES `header` WRITE;
/*!40000 ALTER TABLE `header` DISABLE KEYS */;
INSERT INTO `header` VALUES (42,'Header','Test Header');
/*!40000 ALTER TABLE `header` ENABLE KEYS */;
UNLOCK TABLES;
--
-- Table structure for table `pdf_created`
--
DROP TABLE IF EXISTS `pdf_created`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `pdf_created` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`pdf_id` int(10) NOT NULL,
`item_type` int(3) unsigned NOT NULL,
`item_id` int(10) unsigned NOT NULL,
`quantity` int(3) NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `id` (`id`),
KEY `fk_item_to_product_idx` (`item_id`),
CONSTRAINT `fk_item_to_header` FOREIGN KEY (`item_id`) REFERENCES `header` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
CONSTRAINT `fk_item_to_product` FOREIGN KEY (`item_id`) REFERENCES `product` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
CONSTRAINT `fk_item_to_service` FOREIGN KEY (`item_id`) REFERENCES `service` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Dumping data for table `pdf_created`
--
LOCK TABLES `pdf_created` WRITE;
/*!40000 ALTER TABLE `pdf_created` DISABLE KEYS */;
INSERT INTO `pdf_created` VALUES (1,2,5,42,1);
/*!40000 ALTER TABLE `pdf_created` ENABLE KEYS */;
UNLOCK TABLES;
--
-- Table structure for table `product`
--
DROP TABLE IF EXISTS `product`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `product` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`category_id` int(3) unsigned NOT NULL,
`symbol` varchar(255) DEFAULT NULL,
`desc` varchar(255) DEFAULT NULL,
`price` decimal(5,2) NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `id` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=43 DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Dumping data for table `product`
--
LOCK TABLES `product` WRITE;
/*!40000 ALTER TABLE `product` DISABLE KEYS */;
INSERT INTO `product` VALUES (42,13,'product','desc',10.00);
/*!40000 ALTER TABLE `product` ENABLE KEYS */;
UNLOCK TABLES;
--
-- Table structure for table `service`
--
DROP TABLE IF EXISTS `service`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `service` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`desc` varchar(255) NOT NULL,
`price` decimal(5,2) NOT NULL,
`active` int(1) NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `id` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=43 DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Dumping data for table `service`
--
LOCK TABLES `service` WRITE;
/*!40000 ALTER TABLE `service` DISABLE KEYS */;
INSERT INTO `service` VALUES (42,'some service',5.00,1);
/*!40000 ALTER TABLE `service` ENABLE KEYS */;
UNLOCK TABLES;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;
/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;
-- Dump completed on 2018-08-27 10:31:41
Use 'Data Import/Restore' in MySQL Workbench if you want to test it instead of an SQL script editor.
如果您想测试它而不是 SQL 脚本编辑器，请在 MySQL Workbench 中使用“数据导入/恢复”。
BUTyou should not use auto-increment for the product, headerand servicetables that are referenced by pdf_created.item_id, because it would be a lucky coincidence if all three will get the same id. You should assign the id (the item's id).
但你不应该使用自动增加的product，header并且service由引用的表pdf_created.item_id，因为这将是一个幸运的巧合，如果所有三个会得到相同的ID。您应该分配 id（项目的 id）。
