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

Not possible. You can not use arrays in foreign keys (any direction). You need to model that with a proper many-to-many relationship — , Oct 18 '16 at 21:24

score 0 · Accepted Answer · answered Oct 18 '16 at 21:42

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.

Using an Array in author_id int references user(id)

1 Answers1

You can. But you must not.

with a check constraint

with a trigger and an auxiliar table