Syntax error when adding a new column with a foreign key

Question

I'm trying to add four columns to a table, public.languages, where two of these columns reference the public.users table.

ALTER TABLE languages
    ADD created_by INTEGER NOT NULL, 
    ADD created_at TIMESTAMP NOT NULL,
    ADD modified_by INTEGER NOT NULL,
    ADD modified_at TIMESTAMP NOT NULL,
    FOREIGN KEY(created_by, modified_by) REFERENCES users(user_id);

I can SELECT * from public.users and the user_id column appears just fine.

ERROR:  syntax error at or near "FOREIGN"
LINE 6:  FOREIGN KEY(created_by, modified_by) REFERENCES users(user_...

Answer 1

There are two issues there. One is that you are trying to create a foreign key constraint on two columns, rather than two constraints on one column each. The other is that your syntax is invalid--just throwing the constraints in with the columns works with CREATE TABLE, not with ALTER TABLE. You can fix both of those giving:

ALTER TABLE languages
    ADD created_by INTEGER NOT NULL,
    ADD created_at TIMESTAMP NOT NULL,
    ADD modified_by INTEGER NOT NULL,
    ADD modified_at TIMESTAMP NOT NULL,
    ADD constraint asdfasf FOREIGN KEY (created_by) REFERENCES users(user_id),
    ADD constraint asdfasf2 FOREIGN KEY (modified_by) REFERENCES users(user_id);