Two foreign keys in postgres

Question

I am making a junction table. I want it to have 2 separate foreign keys that reference IDs that are in 2 separate tables.

create table user_book (
    id serial primary key NOT NULL DEFAULT nextval('seq_user_prod_pk'::regclass),
    book_id numeric references books (id) NOT NULL,
    user_id numeric NOT NULL references user (id),
    checked bool,
);

I get the error:

ERROR:  syntax error at or near "user"
LINE 4:     user_id numeric NOT NULL references user (id),
                                                ^

********** Error **********

ERROR: syntax error at or near "user"
SQL state: 42601
Character: 202

Does Postgres not allow 2 foreign keys from two separate tables?

I also tried:

create table user_book (
    id serial primary key NOT NULL DEFAULT nextval('seq_user_prod_pk'::regclass),
    book_id numeric NOT NULL,
    user_id numeric NOT NULL,
    checked bool,
    foreign key (book_id) references book (id)
    foreign key (user_id) references user (id)
);

But got a very similar error.

Solution 1

There's no problem with two, three or a dozen foreign keys. user is a reserved word in Postgres (and, IIRC, in ANSI SQL), so you shouldn't use it as a table name. You could, of course, escape it by using quotes ("):

create table user_book (
    id serial primary key NOT NULL DEFAULT nextval('seq_user_prod_pk'::regclass),
    book_id numeric references books (id) NOT NULL,
    user_id numeric NOT NULL references "user" (id), -- here
    checked bool,
);

But really, it's just a bad choice of name. Switch the name to a non-reserved word (e.g., users), and the problem should go away.

Solution 2

try

create table user_book (
    id serial primary key NOT NULL DEFAULT nextval('seq_user_prod_pk'::regclass),
    book_id numeric references books (id) NOT NULL,
    user_id numeric NOT NULL references "user" (id),
    checked bool,
);

instead...

Comments

[Radek Postołowicz]

It's bad idea to use plural form especially if the rest of the tables are singular. IMO it's better to use user_ instead.