可以在单个字段中使用多个外键吗？

I want to know if there is a way to have multiple values in a single field in a MySQL database where each value is a foreign key referencing one other table.

我想知道是否有一种方法可以在MySQL数据库的单个字段中包含多个值,其中每个值都是引用另一个表的外键。

I am designing a database with a product table and a product certification table.

我正在设计一个带有产品表和产品认证表的数据库。

I am using InnoDB and foreign key constraints.

我正在使用InnoDB和外键约束。

The "product" table contains the details about specific instances of the product. One of the details contained in the product table is the column “product_certification_id”, which is a foreign key referencing an index in the two column “product_certification” table.

“product”表包含有关产品特定实例的详细信息。产品表中包含的详细信息之一是“product_certification_id”列,它是引用两列“product_certification”表中的索引的外键。

The product certification table contains the possible certifications that an instance of a product may have.

产品认证表包含产品实例可能具有的认证。

My problem stems from the fact that the product certifications are not mutually exclusive, so I am curious if it is possible to have multiple foreign key values in the same field referencing the same table.

我的问题源于产品认证不是互斥的,所以我很好奇是否可以在引用同一个表的同一字段中有多个外键值。

Also, I am concerned about the possibility of more certifications being added in the future, so I need to design this in an easily scalable fashion in that sense.

此外,我担心未来可能会增加更多认证,所以我需要在这个意义上以易于扩展的方式设计它。

Thank you for your thoughts.

谢谢你的想法。

3 个解决方案

#1

What you typically do is set up a many to many relationship with an intermediate linking table. Some thing like the following:

您通常所做的是与中间链接表建立多对多的关系。有些事情如下:

CREATE TABLE product (
  `id` integer AUTO_INCREMENT NOT NULL,
  -- other cols --
  PRIMARY KEY (`id`)
);

CREATE TABLE certification (
  `id` integer AUTO_INCREMENT NOT NULL,
  -- other cols --
  PRIMARY KEY (`id`)
);

CREATE TABLE product_certification (
   `product_id` integer NOT NULL,
   `certification_id` integer NOT NULL,
   PRIMARY KEY (`product_id`, `certification_id`),
   CONSTRAINT `product_id_product_id` 
     FOREIGN KEY (`product_id`) 
     REFERENCES `product` (`id`) ON DELETE CASCADE,
   CONSTRAINT `certification_id_certification_id` 
     FOREIGN KEY (`certification_id`) 
     REFERENCES `certification` (`id`) ON DELETE CASCADE
);

#2

If I understand you correctly, the relation product : product_certification is 1:M you can create a foreign key from product_certification to products via product_id, instead of having product_certification_id in the products table (which is invalid, since the product can have more than 1 certification)

如果我理解正确,关系product:product_certification为1:M您可以通过product_id创建从product_certification到产品的外键,而不是product表中的product_certification_id(这是无效的,因为该产品可以有多于1个认证)

#3

A single field cannot be a foreign key to more than one field in another table. It's just not possible. Since your foreign table has a composite key, your table in question would have to have the same fields as well.

单个字段不能是另一个表中多个字段的外键。这是不可能的。由于您的外表具有复合键,因此您的表必须具有相同的字段。