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

问题描述

我正在尝试为我的应用创建数据库，我想找到最好的方法是在我的用户和 Items 表。

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，但是我d想知道是否有可能在 Users 中创建一列，例如 reviewedItems ，这是一个包含以下内容的整数数组用户已审核的项目的外键。

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