9

I have the following tables,

CREATE TABLE users (id int PRIMARY KEY);

-- already exists with data
CREATE TABLE message ();

How do I alter messages table such that,

  1. a new column called sender is added to it
  2. where sender is a foreign key referencing the users table

This didn't work

# ALTER TABLE message ADD FOREIGN KEY (sender) REFERENCES users;
ERROR:  column "sender" referenced in foreign key constraint does not exist

Does this statement not create the column as well?

  • 3
    You need to create the column before you reference it. I would also try reading the documentation for ALTER TABLE here, and pay very close attention to the examples. – Kassandry Mar 12 '16 at 5:48
  • Hassan, I cleaned up this question to use DDL and I removed the things that weren't working. See if this answers the question: dba.stackexchange.com/a/202564/2639 . Feel free to reject any of these edits, I just wanted to clean this up for posterity. – Evan Carroll Mar 28 '18 at 17:38
  • @Kassandry dba.stackexchange.com/a/202564/2639 – Evan Carroll Mar 28 '18 at 19:25
14

What you want exactly is not possible however, it is relatively easy. The column has to exist in order to make it an FK. I did the following (from here and the documentation):

CREATE TABLE x(t INT PRIMARY KEY);

CREATE TABLE y(s INT);

ALTER TABLE y ADD COLUMN z INT;    

ALTER TABLE y
  ADD CONSTRAINT y_x_fkey FOREIGN KEY (z)
      REFERENCES x (t)
      ON UPDATE CASCADE ON DELETE CASCADE;

A couple of points to note:

  • ALWAYS give your foreign keys reasonably legible names. Being told that key "XYZ_0004B" is being violated is about as useful as a chocolate tea-pot! Oracle is great for this if you don't name your FKs.

  • Your desired feature, while perhaps being a "nice to have" isn't really critical, since DDL is NOT something you want your application to be doing on a regular basis. It also risks adding to an already fairly substantial documentation.

  • 1
    I don't ever name my foreign keys. They get autonamed, and they're usually pretty useful. For instance, the default name in that context is "y_z_fkey". I'd argue that's a better name than y_x_fkey because your violation doesn't tell you the column you're inserting into that's causing the error. I care less about where it's pointing. As a general rule, you should NEVER name your fkeys and let PostgreSQL's default handle it. – Evan Carroll Mar 28 '18 at 17:26
  • Also, you may not want to override the defaults of ON UPDATE CASCADE ON DELETE CASCADE; in an example either, especially without reason. It makes the example more complex, and you don't bother explaining what it is. I for one don't normally want deletes to cascade. – Evan Carroll Mar 28 '18 at 17:27
  • 1
    I always name FKs, according to the convention that the company/project has decided. It doesn't matter much if it is y_x_fkey or y_z_fkey or x__y_FK, as long as it is consistent. – ypercubeᵀᴹ Mar 28 '18 at 18:36
  • I would very much agree with this if you're contracting - pick a convention and stick to it and/or ensure that you conform to the convention(s) that was/were used with the system previously. – Vérace Mar 28 '18 at 22:14
  • @EvanCarroll - if PostgreSQL's convention is that of the project or one previously decided on systems which might not be PostgreSQL - a system may well have started out on, say, Oracle or other system which might not have PostgreSQL's convention(s). You could argue that x_y_z_fk might give the maximum possible information in the event of an error! Pick something and stick to it is my motto, but don't let one RDBMS (no matter how good) decide conventions for you! – Vérace Mar 28 '18 at 22:19
4

I'm not sure why everyone is telling you that you have to do this in two steps. In fact, you don't. You tried to add a FOREIGN KEY which assumes, by design, the column is there and throws that error if the column is not there. If you add the COLUMN, you can explicitly make it a FOREIGN KEY on creation with REFERENCES,

ALTER TABLE message
  ADD COLUMN sender INT
  REFERENCES users;  -- or REFERENCES table(unique_column)

Will work fine. You can see the syntax of ALTER TABLE here,

ALTER TABLE [ IF EXISTS ] [ ONLY ] name [ * ]
action [, ... ]

With "action" as,

ADD [ COLUMN ] [ IF NOT EXISTS ] column_name data_type [ COLLATE collation ] [ column_constraint [ ... ] ]

These examples are even in the docs,

ALTER TABLE distributors
  ADD CONSTRAINT distfk
  FOREIGN KEY (address)
  REFERENCES addresses (address);

ALTER TABLE distributors
  ADD CONSTRAINT distfk
  FOREIGN KEY (address)
  REFERENCES addresses (address)
  NOT VALID;

But all that isn't needed because we can rely on autonaming and the primary-key resolution (if only the table-name is specified then you're referencing the primary key).

0

CASE1: If you need to create foreign key while creating a new table

CREATE TABLE table1(
id SERIAL PRIMARY KEY,
column1 varchar(n) NOT NULL,
table2_id SMALLINT REFERENCES table2(id)
); 

The above commands will create a table with name 'table1' and three columns named 'id'(Primary key), 'column1', 'table2_id'(foreign key of table1 that references id column of table2).

DATATYPE 'serial' will make the column that uses this datatype as a auto-generated column, when inserting values into the table you need not mention this column at all, or you can give 'default' without quotes at the value place.

A primary key column is always added to index of the table with value 'tablename_pkey'.

If foreign key is added at table creation time, A CONSTRAINT is added with pattern '(present_table_name)_(foreign_key_id_name)_fkey'.

When adding a foreign key, we have to input the keyword 'REFERENCES' next to column name because we want to tell the postgres that this column references a table and then next to references we have to give the table for reference and in brackets give the column name of the referenced table, usually foreign keys are given as primary key columns.

CASE 2: If you want foreign key to an existing table on existing column

ALTER TABLE table1
ADD CONSTRAINT table1_table2_id_id_fkey
FOREIGN KEY (table2_id) REFERENCES table2(id);

NOTE: brackets'()' after FOREIGN KEY and REFERENCES tabel2 are compulsory or else postgres will throw error.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.