postgresqlpostgraphql

Using an Array in author_id int references user(id)

On https://github.com/calebmer/postgraphql the author shows a schema:

create table post (
  id serial primary key,
  author_id int non null references user(id),
  headline text,
  body text,
  …
);

How would I have to rewrite the schema if I would like to cite multiple authors and reference them using an array? Would it be: author_id int[] non null references user(id)?

Solution

  • You can. But you must not.

    with a check constraint

    You can add a check constraint with a function that controls that.

    Disadvantage: It's not a foreign key

    with a trigger and an auxiliar table

    You can add a table post_author and populate it with a On insert or update trigger.

    Disadvantage: It's triky and duplicates info.