PostgreSQL 元素数组，每个元素都是外键
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/41054507/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
PostgreSQL array of elements that each are a foreign key
提问by Zach
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 Usersand Itemstables.
我正在尝试为我的应用程序创建一个数据库，我想找到的最好方法是在我的表Users和Items表之间创建一对多的关系。
I know I can make a third table, ReviewedItems, and have the columns be a Userid and an Itemid, 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 Itemsthat the Userhas reviewed.
我知道我可以做一个第三个表，ReviewedItems和具有列是一个UserID和一个ItemID，但我想知道是否有可能使一列Users，让我们说reviewedItems，这是一个包含外键的整数数组Items是该User。
If PostgreSQL can do this, please let me know! If not, I'll just go down my third table route.
如果 PostgreSQL 可以做到这一点，请告诉我！如果没有，我就走我的第三条餐桌路线。
采纳答案by Patrick
No, this is not possible.
不，这是不可能的。
PostgreSQL is a relationalDBMS, 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.
PostgreSQL 是一个关系型DBMS，在正确规范化的数据模型上运行效率最高。数组 - 根据定义，它们是有序集 - 不是关系数据结构，因此 SQL 标准不支持在数组元素上定义外键，PostgreSQL 也不支持。
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.
但是，您可以使用链接到其他表中的主键的数组元素构建一个完美的数据库。但是，这些数组元素不能被声明为外键，因此 DBMS 将不会保持引用完整性。
回答by Jarym
It may soon be possible to do this: https://commitfest.postgresql.org/17/1252/- Mark Rofail has been doing some excellent work on this patch!
很快就可以做到这一点：https://commitfest.postgresql.org/17/1252/ - Mark Rofail 在这个补丁上做了一些出色的工作！
The patch will (once complete) allow
该补丁将（一旦完成）允许
CREATE TABLE PKTABLEFORARRAY (
ptest1 float8 PRIMARY KEY,
ptest2 text
);
CREATE TABLE FKTABLEFORARRAY (
ftest1 int[],
FOREIGN KEY (EACH ELEMENT OF ftest1) REFERENCES PKTABLEFORARRAY,
ftest2 int
);
However, author currently needs help to rebase the patch (beyond my own ability) so anyone reading this who knows Postgres internals please help if you can.
然而，作者目前需要帮助来重新调整补丁（超出我自己的能力）所以任何阅读本文的人都知道 Postgres 内部结构，如果可以的话，请提供帮助。
