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

问题描述

我想知道是否有办法在MySQL数据库的单个字段中有多个值，其中每个值都是引用另一个表的外键。 >我正在设计一个产品表和产品认证表的数据库。

我使用InnoDB和外键约束。

产品表包含有关产品特定实例的详细信息。产品表中包含的其中一个详细信息是product_certification_id列，该列是引用两列product_certification表中的索引的外键。产品认证表包含产品实例可能具有的认证。

我的问题源于产品认证不互相排斥的事实，所以我很好奇是否可以在同一个字段中引用多个外键值同样的表

另外，我担心将来可能会增加更多的认证，所以我需要在这个意义上以易于扩展的方式进行设计。

感谢您的想法。

解决方案

与中间链接表建立了多对多的关系。如下所示：

  CREATE TABLE product（
`id` integer AUTO_INCREMENT NOT NULL，
 - 其他列 -  
 PRIMARY KEY（`id`）
）; 
 
 CREATE TABLE认证（
`id`整数AUTO_INCREMENT不是NULL，
  - 其他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`证书`（`id`）ON DELETE CASCADE 
）; 
  

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.

I am using InnoDB and foreign key constraints.

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.

Thank you for your thoughts.

解决方案

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屋！