Postgres: Referencing a foreign key that's part of an array

Question

With Postgres, I've created many tables that have foreign key references but they've always had a 1:1 relationship. Now I'd like to do something a little different:

CREATE TABLE public.shared_media (
  share_id uuid NOT NULL UNIQUE DEFAULT uuid_generate_v4(),
  media_ids uuid[] NOT NULL,
  description text NULL,
  intro_message text NULL,
  embedded bool NOT NULL,
  export_options json NULL,
  user_id uuid NOT NULL,
  date_created timestamptz NOT NULL DEFAULT now(),
  date_deleted timestamptz NULL,
  CONSTRAINT fk_media 
    FOREIGN_KEY(media_id) 
      REFERENCES media(media_id)
    CONSTRAINT fk_users
      FOREIGN KEY(user_id)
          REFERENCES users(user_id)
);

The 3rd line refers to an array of media_id values; media_id being the primary key in my media table.

The SQL code above fails to work because of:

  CONSTRAINT fk_media 
    FOREIGN_KEY(media_id) 
      REFERENCES media(media_id)

I understand why. I tried substituting the original 3rd line with: media_ids media_id[] NOT NULL, but that didn't work either.

I've done some reading and a bridge table is suggested by some. While I understand this thinking, this shared_media table will rarely be accessed other than providing the data it contains. In other words, it'll never be searched, which is why I'm comfortable using the media_ids uuid[] approach.

Dropping the fk_media constraint does allow the table to be created. Given what I'm going to use shared_media for, would you be happy with this approach in your own project?

JohnH · Answer 1 · 2023-06-18T19:48:36.380

Do not store references in arrays; use associative (bridge) tables. This recommendation is based on referential integrity and code maintenance concerns. How often the data is queried or searched is not relevant.

Using an associative table with appropriate foreign key constraints takes advantage of the database platform's native referential integrity support. This approach also makes the schema self-documenting since the associations between tables can be ascertained by querying the system catalogs and views.

While it is possible to store references in arrays and use triggers to provide referential integrity support, such an approach requires significantly more coding effort and presents many opportunities to introduce defects. This approach also makes it more difficult for others to become familiar with the design since the associations are captured in code and external documentation instead of being inherent elements of the schema.

The NOT NULL constraint on media_ids that is in the original table definition could be replaced with a deferred after statement trigger to insure that at least one media reference is present when the transaction is committed.

Erwin Brandstetter · Accepted Answer · 2023-06-22T14:55:05.137

You are trying to reference uuid with uuid[]. But a FK reference requires matching data types.

Foreign key references for array elements are not implemented in Postgres. (Not sure if any other RDBMS implements that.) See:

Can PostgreSQL array be optimized for join?

Either keep the array and give up enforcing referential integrity. There are workarounds with triggers, but not nearly as simple, safe and cheap. A related case with code example:

Can PostgreSQL have a uniqueness constraint on array elements?

Or normalize your design with a "bridge table" - a proper many-to-many implementation that enforces referential integrity at all times. See:

How to implement a many-to-many relationship in PostgreSQL?

Aside, media_ids uuid[] NOT NULL does not enforce "at least one" reference. The array can still be empty ([]). Not sure if that's the intention.

Postgres: Referencing a foreign key that's part of an array

2 Answers2