将列添加为外键会使外键约束中引用的ERROR列不存在 [英] Adding a column as a foreign key gives ERROR column referenced in foreign key constraint does not exist

问题描述

我进行了以下设置，

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

我正在尝试添加列，名为发件人到 links_chatpicmessage ，这是另一个名为 auth_user 的 id 列。

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;

但这给我一个错误：

错误：外键约束中引用的列发件人不存在

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

如何我要解决这个问题吗？

How do I fix this?

推荐答案

向列添加约束它首先需要存在于表中在Postgresql中没有可以使用的命令将同时添加列和添加约束。它必须是两个单独的命令。您可以使用以下命令进行操作：

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:

首先要执行以下操作：

ALTER TABLE links_chatpicmessage ADD COLUMN sender INTEGER;

我在这里使用 integer 作为类型，但它应该与 auth_user 表的 id 列的类型相同。

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

然后添加约束

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

此命令的 ADD CONSTRAINT fk_someName 部分是命名约束，因此，如果您以后需要使用一些用于创建模型的工具对其进行记录，则将具有命名约束而不是随机名称。

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.

它也可以满足管理员的需求，因此DBA知道约束来自该表。

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

通常，我们用它从何而来的名称来暗示它的名称。它在您的案例中引用的是 fk_links_chatpicmessage_auth_user ，因此任何看到此名称的人都将确切知道此约束是什么，而无需在INFORMATION_SCHEMA上进行复杂的查询即可找到。

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.

编辑

如@btubbs的答案所述，您实际上可以在其中添加带有约束的列一个命令。像这样：

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

这篇关于将列添加为外键会使外键约束中引用的ERROR列不存在的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！