将列添加为外键会导致外键约束中引用的 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 ();

我正在尝试将一个名为 sender 的列添加到 links_chatpicmessage，这是另一个名为 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 部分是命名你的约束，所以如果你以后需要用一些创建模型的工具来记录它，你将有一个命名的约束而不是随机名称.

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