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

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?

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]

To add a constraint to a column It needs to exists first into the table <s>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.</s> 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);

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 see links please register here]

)

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

****foreign key reference for existing column****

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

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".