151

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.

Zach
  • 4,145
  • 8
  • 28
  • 52
  • 4
    There have been patches to add this feature to Postgres, see https://blog.2ndquadrant.com/postgresql-9-3-development-array-element-foreign-keys/ (2012) and https://www.postgresql.org/message-id/CAJvoCuuEgQ%2Bpjv02rjvQN4o9s4_H3HJMdZdQwss2b0c0-xagEw%40mail.gmail.com (2017). They haven't been accepted as yet, but hopefully one day. – Simon Kissane Sep 10 '17 at 00:26

2 Answers2

129

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!

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.

amphetamachine
  • 25,180
  • 10
  • 57
  • 71
Jarym
  • 1,635
  • 1
  • 12
  • 12
  • 11
    very HOT indeed... `CREATE TABLE FKTABLEFORARRAY ( ftest1 int[], FOREIGN KEY (EACH ELEMENT OF ftest1) REFERENCES PKTABLEFORARRAY, ftest2 int )` – Victor Jun 19 '18 at 14:45
  • 1
    The patch is awaiting author to follow up. Until the feature is available, you will have to use trigger to check integrity which may be slow. – yoonghm Jul 09 '18 at 08:58
  • 51
    shame, it doesn't seems to have been included :( – Teocali Jul 24 '19 at 17:20
  • 47
    2020, still unsupported: https://www.postgresql.org/docs/13/unsupported-features-sql-standard.html – M Imam Pratama Sep 24 '20 at 12:49
  • 3
    This is really useful. – karianpour Feb 02 '21 at 10:22
  • 7
    Per the [patch email thread](https://www.postgresql.org/message-id/flat/CAJvoCut7zELHnBSC8HrM6p-R6q-NiBN1STKhqnK5fPE-9=Gq3g@mail.gmail.com), as of July 2021 it appears that this "still requires some major surgery." So we could still have a while. – user45392 Aug 15 '21 at 03:57
  • Holy moly it's 2022 and still unsupported. – m4heshd May 22 '22 at 06:23
104

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.

Patrick
  • 26,336
  • 6
  • 57
  • 84
  • 4
    You _can_ define a constraint trigger that would check that. But I'm not sure if it works reliably in all cases. – a_horse_with_no_name Dec 09 '16 at 06:59
  • @a_horse_with_no_name: could you give an example about `reliably in all cases` ? You mean sometimes trigger can be failed ? Thanks. – Luan Huynh Dec 09 '16 at 07:09
  • 1
    @LuanHuynh: I don't recall the (technical) details, but the last time this was discussed on the mailing list someone mentioned that a corresponding constraint trigger might not catch all cases - but that might no longer be true – a_horse_with_no_name Dec 09 '16 at 07:14
  • Thanks, I guess I'll just make a relationship table for them – Zach Dec 09 '16 at 23:26
  • 2
    @a_horse_with_no_name can you post an answer with an example of such a constraint? – OrangeDog Jan 18 '19 at 15:35
  • does it increase performance somehow ? Or should someone stick to the third table approach ? – Muhammad Asim Feb 14 '20 at 11:34
  • 1
    workaround / re-design: having a separate many-to-many table – Jean Monet Feb 05 '21 at 20:45
  • 6
    Apparently, [sql:2016](https://en.wikipedia.org/wiki/SQL:2016) _does_ support this behavior. Unfortunately the standard isn't freely available, but you can see it referenced in both [Microsoft's documentation](https://docs.microsoft.com/en-us/openspecs/sql_standards/ms-tsqliso02/ef50d8ea-b423-4ed4-ab1e-ae7b2882eb8b) and [PostgreSQL's list of unsupported standard features](https://www.postgresql.org/docs/13/unsupported-features-sql-standard.html). – Phil Frost Jul 22 '21 at 16:43