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

Answer 2

You can do this in Postgres on one line:

ALTER TABLE links_chatpicmessage 
    ADD COLUMN sender INTEGER 
    REFERENCES auth_user (id);

You don't need to manually set a name. Postgres will automatically name this constraint "links_chatpicmessage_auth_user_id_fkey".

Answer 3

I know this answer is way late, and I realize this is the same as btubbs one-liner, just a little more descriptive ...

Assuming you want to reference the primary key in table auth_user and that key name is 'id'.

I use this syntax:

ALTER TABLE links_chatpicmessage 
ADD COLUMN sender some_type,
ADD FOREIGN KEY (sender) REFERENCES auth_user(id);

Note: some_type = [type the same as sender in table auth_user]

Answer 4

The CONSTRAINT clause is optional. I suggest ommiting it and always letting PostgreSQL autoname the constraint, without naming it you'll get a logical name

"links_chatpicmessage_sender_fkey" FOREIGN KEY (sender) REFERENCES auth_user(id)

That's what you'll likely want to know if an INSERT or UPDATE fails due to a constraint violation.

Syntax to add a foreign key

All of these are somewhat documented on ALTER TABLE

To a new column

ALTER TABLE links_chatpicmessage 
  ADD COLUMN sender int,
  ADD [CONSTRAINT foo] FOREIGN KEY (sender) REFERENCES auth_user(id);

This is compound and transactional. You can issue two ALTER statements on the same table by separating the two statements with a ,.

To a preexisting column

-- assumes someone has already added the column or that it already exists
ALTER TABLE links_chatpicmessage
  ADD COLUMN sender int;

ALTER TABLE links_chatpicmessage
  ADD [CONSTRAINT foo] FOREIGN KEY (sender) REFERENCES auth_user(id);

Answer 5

foreign key reference for existing column

ALTER TABLE table_name
ADD CONSTRAINT fkey_name
FOREIGN KEY (id)
REFERENCES ref_table(id)