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
);
这篇关于单个字段中是否有多个外键?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持编程学习网!
本文标题为:单个字段中是否有多个外键?
- SoapClient 设置自定义 HTTP Header 2021-01-01
- 如何定位 php.ini 文件 (xampp) 2022-01-01
- Laravel 仓库 2022-01-01
- Mod使用GET变量将子域重写为PHP 2021-01-01
- 正确分离 PHP 中的逻辑/样式 2021-01-01
- 从 PHP 中的输入表单获取日期 2022-01-01
- Oracle 即时客户端 DYLD_LIBRARY_PATH 错误 2022-01-01
- 没有作曲家的 PSR4 自动加载 2022-01-01
- 带有通配符的 Laravel 验证器 2021-01-01
- PHP Count 布尔数组中真值的数量 2021-01-01