PostgreSQL array of elements that each are a foreign key

postgresql foreign-keys foreign-collection
Zach picture Zach · Dec 9, 2016 · Viewed 38.5k times · Source

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.

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.

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

Answer

Patrick picture Patrick · Dec 9, 2016

No, this is not possible.

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.

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.

Related questions

Delete rows with foreign key in PostgreSQL

I would like to delete rows which contain a foreign key, but when I try something like this: DELETE FROM osoby WHERE id_osoby='1' I get this statement: ERROR: update or delete on table "osoby" violates foreign key …

Read More
Postgres and Indexes on Foreign Keys and Primary Keys

Does Postgres automatically put indexes on Foreign Keys and Primary Keys? How can I tell? Is there a command that will return all indexes on a table?

Read More
Adding a column as a foreign key gives ERROR column referenced in foreign key constraint does not exist

I have the following set up, CREATE TABLE auth_user ( id int PRIMARY KEY ); CREATE TABLE links_chatpicmessage (); I'm trying to add a column named sender to links_chatpicmessage which is a foreign key to another table called auth_user's …

Read More