Como adicionar uma coluna com uma restrição de chave estrangeira a uma tabela que já existe?

Você só precisa adicionar outra etapa - na verdade, o PostgreSQL já está lhe dizendo que: column "sender" referenced in foreign key constraint does not exist.

A coluna FOREIGN KEY(aka ) já deve existir para torná-la um .parentFK

Eu fiz o seguinte (a partir daqui e da documentação ). Observe que a coluna pai precisa ter uma UNIQUErestrição (ou ser o PRIMARY KEY), mas não precisa ser NOT NULL. Isso ocorre porque NULLs não são iguais entre si, nem são iguais a qualquer outra coisa - cada um NULLé considerado UNIQUEem seu próprio direito!

CREATE TABLE x(a INT UNIQUE NOT NULL);

CREATE TABLE y(b INT);

ALTER TABLE y ADD COLUMN c INT NOT NULL
CONSTRAINT y_x_fk_c REFERENCES x (a)   -- if x (a) doens't exist, this will fail!
ON UPDATE CASCADE ON DELETE CASCADE;  -- or other Referential Integrity Action

Alguns pontos a serem observados (veja o violino aqui ) - uma tentativa de inserir um valor em y (c) que não está em x (a) falha e o nome da restrição é fornecido na mensagem de erro.

O violino tem NOT NULLrestrições em x (a) e em y (c). A menos que eu tenha um motivo realmente convincente , sempre declaro minhas colunas como NOT NULL- isso ajuda o otimizador e reduz o potencial de confusão/erro. Você pode experimentar o violino para ver o que acontece quando você deixa de fora o NOT NULL(s) campo(s) - o comportamento nem sempre é intuitivamente óbvio!

SEMPRE dê nomes significativos às suas chaves estrangeiras. Ser informado de que a chave "SYS_C00308108" está sendo violada não é muito útil. Veja o violino aqui para o comportamento do Oracle sob essas circunstâncias, o nome da chave irá variar de violino para violino, mas é uma string arbitrária começando com SYS_... (vem após o nome da tabela gerado pelo dbfiddle longo).

Evan Carroll em sua resposta aqui acredita que nomes gerados automaticamente estão OK - eu mostrei por que isso não é uma boa ideia para o Oracle (pelo menos até 18c), mas também sinto que não é uma boa ideia para o PostgreSQL - problemas potenciais de portabilidade, se nada mais.

Eu gostaria de dar crédito a Evan Carroll por apontar que a adição do novo campo e a FOREIGN KEYcriação e o CONSTRAINT(com o nome especificado) podem ser adicionados em uma etapa e não em duas etapas como eu disse originalmente) - então, por favor, dê-lhe crédito por isso se você sentir vontade de me votar - eu entro em mais detalhes no entanto.

Considerando a afirmação em sua pergunta:

ALTER TABLE message ADD FOREIGN KEY (sender) REFERENCES users;

Seria "legal" se o RDBMS pudesse criar automaticamente o campo desejado com o tipo de dados correspondente ao campo referenciado.

Tudo o que eu diria é que alterar o DDL é (ou pelo menos deveria ser) uma operação raramente usada e não algo que você gostaria de fazer regularmente. Também corre o risco de adicionar a uma documentação já bastante substancial.

Pelo menos o PostgreSQL tenta fazer algo razoável - ele concatena o nome da tabela, o FOREIGN KEYnome do campo e fkey. Além disso, quando você mesmo nomear a restrição, a mensagem de erro será adicionada DETAIL: Key (c)=(7) is not present in table "x".para fornecer algo que possa fazer sentido para um ser humano (diferente do Oracle - veja o final do violino do PostgreSQL ).

Não sei por que todos estão dizendo que você precisa fazer isso em duas etapas. Na verdade, você não . Você tentou adicionar um FOREIGN KEYque assume, por design, que a coluna está lá e lança esse erro se a coluna não estiver lá. Se você adicionar o COLUMN, poderá torná-lo explicitamente FOREIGN KEYna criação com REFERENCES,

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

Vai funcionar bem. Você pode ver a sintaxe de ALTER TABLEaqui,

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

Com "ação" como,

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

Esses exemplos estão até nos documentos,

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;

Mas tudo isso não é necessário porque podemos confiar na autonomeação e na resolução da chave primária (se apenas o nome da tabela for especificado, você estará referenciando a chave primária).

CASE1: Se você precisar criar uma chave estrangeira ao criar uma nova tabela

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

Os comandos acima criarão uma tabela com o nome 'table1' e três colunas denominadas 'id'(chave primária), 'column1', 'table2_id'(chave estrangeira da tabela1 que faz referência à coluna id da tabela2).

DATATYPE 'serial' fará com que a coluna que usa esse tipo de dados seja uma coluna gerada automaticamente, ao inserir valores na tabela você não precisa mencionar essa coluna, ou pode dar 'default' sem aspas no lugar do valor.

Uma coluna de chave primária é sempre adicionada ao índice da tabela com o valor 'tablename_pkey'.

Se uma chave estrangeira for adicionada no momento da criação da tabela, uma CONSTRAINT será adicionada com o padrão '(present_table_name)_(foreign_key_id_name)_fkey'.

Ao adicionar uma chave estrangeira, temos que inserir a palavra-chave 'REFERENCES' ao lado do nome da coluna porque queremos informar ao postgres que esta coluna faz referência a uma tabela e, ao lado de referências, devemos fornecer a tabela para referência e entre colchetes, fornecer o nome da coluna da tabela referenciada, geralmente as chaves estrangeiras são fornecidas como colunas de chave primária.

CASO 2: Se você deseja chave estrangeira para uma tabela existente na coluna existente

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

NOTA: colchetes '()' após FOREIGN KEY e REFERENCES tabel2 são obrigatórios ou então o postgres lançará um erro.

Eu sei o problema. Os nomes das colunas são diferentes. Talvez em uma coluna, haja um espaço adicionado após o nome da coluna, portanto, certifique-se cuidadosamente de que os nomes das colunas tenham o mesmo nome.