添加列作为外键会给出外键约束中引用的 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 添加一个名为 sender 的列，它是另一个名为 auth_user<的表的外键/code> 的 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 部分是 naming 您的约束，因此如果您以后需要使用一些创建模型的工具对其进行记录，您将拥有一个命名约束而不是随机名称.

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屋！