Search code examples
postgresqldatabase-designforeign-keys

Referencing a foreign key that's part of an array

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?

Solution

  • 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:

    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:

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

    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.