database - Adding a column as a foreign key gives ERROR column referenced in foreign key constraint does not exist

Question

I have the following set up,

CREATE TABLE auth_user ( id int PRIMARY KEY );
CREATE TABLE links_chatpicmessage ();

I'm trying to add a column named sender to links_chatpicmessage which is a foreign key to another table called auth_user's id column.

To achieve the above, I'm trying the following on terminal:

ALTER TABLE links_chatpicmessage
  ADD FOREIGN KEY (sender)
  REFERENCES auth_user;

But this gives me an error:

ERROR: column "sender" referenced in foreign key constraint does not exist

How do I fix this?

Answer 1

To add a constraint to a column It needs to exists first into the table there is no command in Postgresql that you can use that will add the column and add the constraint at the same time. It must be two separate commands. You can do it using following commands:

First do as:

ALTER TABLE links_chatpicmessage ADD COLUMN sender INTEGER;

I use integer as type here but it should be the same type of the id column of the auth_user table.

Then you add the constraint

ALTER TABLE links_chatpicmessage 
   ADD CONSTRAINT fk_someName
   FOREIGN KEY (sender) 
   REFERENCES auth_user(column_referenced_name);

The ADD CONSTRAINT fk_someName part of this command is naming your constraint so if you latter on need to document it with some tool that create your model you will have a named constraint instead of a random name.

Also it serves to administrators purposes so A DBA know that constraint is from that table.

Usually we name it with some hint about where it came from to where it references on your case it would be fk_links_chatpicmessage_auth_user so anyone that sees this name will know exactly what this constraint is without do complex query on the INFORMATION_SCHEMA to find out.

EDIT

As mentioned by @btubbs's answer you can actually add a column with a constraint in one command. Like so:

alter table links_chatpicmessage 
      add column sender integer, 
      add constraint fk_test 
      foreign key (sender) 
      references auth_user (id);