Using an Array in author_id int references user(id)

Question

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)?

Answer 1

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.