单个字段中是否有多个外键?
我想知道是否有一种方法可以在 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
);
相关文章