单个字段中是否有多个外键? [英] Are multiple foreign keys in a single field possible?

问题描述

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

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.

我正在设计一个包含产品表和产品认证表的数据库.

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

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

I am using InnoDB and foreign key constraints.

产品"表包含有关产品特定实例的详细信息.产品表中包含的详细信息之一是列product_certification_id"，它是引用两列product_certification"表中的索引的外键.

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.

产品认证表包含产品实例可能拥有的认证.

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.

感谢您的意见.

推荐答案

您通常做的是与中间链接表建立多对多关系.类似于以下内容:

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
);

这篇关于单个字段中是否有多个外键?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！