PostgreSQL 元素数组，每个元素都是外键 [英] PostgreSQL array of elements that each are a foreign key

问题描述

我正在尝试为我的应用程序创建一个数据库，我想找到的最好方法是在我的 Users 和 之间创建一对多关系项目表格.

I am attempting to create a DB for my app and one thing I'd like to find the best way of doing is creating a one-to-many relationship between my Users and Items tables.

我知道我可以创建第三个表，ReviewedItems，并让列成为一个 User id 和一个 Item id，但是我想知道是否可以在 Users 中创建一列，比如说 reviewedItems，它是一个包含 Items 外键的整数数组User 已审核.

I know I can make a third table, ReviewedItems, and have the columns be a User id and an Item id, but I'd like to know if it's possible to make a column in Users, let's say reviewedItems, which is an integer array containing foreign keys to Items that the User has reviewed.

如果 PostgreSQL 可以做到这一点，请告诉我！如果没有，我就走我的第三条餐桌路线.

If PostgreSQL can do this, please let me know! If not, I'll just go down my third table route.

推荐答案

不，这是不可能的.

PostgreSQL 是一个关系 DBMS，在正确规范化的数据模型上运行最有效.数组 - 根据定义，它们是有序集 - 不是关系数据结构，因此 SQL 标准不支持在数组元素上定义外键，PostgreSQL 也不支持.

PostgreSQL is a relational DBMS, operating most efficiently on properly normalized data models. Arrays are - by definition, they are ordered sets - not relational data structures and the SQL standard therefore does not support defining foreign keys on array elements, and neither does PostgreSQL.

但是，您可以使用链接到其他表中的主键的数组元素构建完美的数据库.然而，这些数组元素不能被声明为外键，因此 DBMS 将不会保持引用完整性.

You can, however, build a perfectly fine database with array elements linking to primary keys in other tables. Those array elements, however, can not be declared to be foreign keys and the DBMS will therefore not maintain referential integrity.

这篇关于PostgreSQL 元素数组，每个元素都是外键的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！