可以将一列引用为多个外键

I have few tables, and I want to reference one column from PDF table to multiple other tables. 可以将一列引用为多个外键

我有几个表，我想将PDF表中的一列引用到其他表中。

for example if PDF table select output looks like this:

例如，如果PDF表选择输出如下所示:

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?

有可能创造它吗?

6 个解决方案

#1

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 TABLES won'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表中的每个外键在所有四个表中都有一个主键，这对您不起作用。因此，继续使用该字段引用其他记录，但不要创建任何外键约束。

#2

The problem you met is called Polymorphic Associations

你遇到的问题叫做多态关联

Please refer this question: MySQL - Conditional Foreign Key Constraints

请参考这个问题:MySQL -条件外键约束

#3

It should be possible. One potential problem is that your three foreign key constraints have the same name.

它应该是可能的。一个潜在的问题是，您的三个外键约束具有相同的名称。

#4

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.

您需要的是一个记录(行)来引用表产品、头和服务中的记录。方法是使用三个不同的字段，每个字段对应一个外键。

I also notice that the Item table has the three foreign keys needed. You could have the PDF table have one field which references Item, and the record in Item references the three other tables.

我还注意到Item表具有所需的三个外键。您可以让PDF表有一个引用项的字段，而项中的记录引用其他三个表。

#5

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..

它可能的外键约束名称应该与此不同，主键和外键表列应该具有相同的数据类型。

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$$

#6

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.

理论上，不能在单个列上强制执行多个外键。或者，您也可以使用过程来执行此操作，其中验证存在于多个表中的输入并执行必要的操作。请注意，该表上的所有操作都应该由验证必要条件的程序完成，否则会导致违反完整性的情况。

#1

No.

不。