database 将列添加为外键会导致外键约束中引用的 ERROR 列不存在
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/35676149/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Adding a column as a foreign key gives ERROR column referenced in foreign key constraint does not exist
提问by Hassan Baig
I have the following set up,
我有以下设置，
CREATE TABLE auth_user ( id int PRIMARY KEY );
CREATE TABLE links_chatpicmessage ();
I'm trying to add a columnnamed senderto links_chatpicmessagewhich is a foreign key to another table called auth_user's idcolumn.
我正在尝试添加一个名为senderto的列，links_chatpicmessage该列是另一个名为auth_user'sid列的表的外键。
To achieve the above, I'm trying the following on terminal:
为了实现上述目标，我正在终端上尝试以下操作：
ALTER TABLE links_chatpicmessage
ADD FOREIGN KEY (sender)
REFERENCES auth_user;
But this gives me an error:
但这给了我一个错误：
ERROR: column "sender" referenced in foreign key constraint does not exist
错误：外键约束中引用的列“发件人”不存在
How do I fix this?
我该如何解决？
回答by Jorge Campos
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:
向列添加约束它需要首先存在于表中，在 Postgresql 中没有可以使用的命令来添加列并同时添加约束。它必须是两个单独的命令。您可以使用以下命令执行此操作：
First do as:
首先做如下：
ALTER TABLE links_chatpicmessage ADD COLUMN sender INTEGER;
I use integeras type here but it should be the same type of the idcolumn of the auth_usertable.
我integer在这里使用as 类型，但它应该id与auth_user表的列类型相同。
Then you add the constraint
然后你添加约束
ALTER TABLE links_chatpicmessage
ADD CONSTRAINT fk_someName
FOREIGN KEY (sender)
REFERENCES auth_user(column_referenced_name);
The ADD CONSTRAINT fk_someNamepart of this command is namingyour 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.
ADD CONSTRAINT fk_someName这个命令的一部分是命名你的约束，所以如果你以后需要用一些创建模型的工具来记录它，你将拥有一个命名约束而不是随机名称。
Also it serves to administrators purposes so A DBA know that constraint is from that table.
它还用于管理员目的，因此 DBA 知道约束来自该表。
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_userso anyone that sees this name will know exactly what this constraint is without do complex query on the INFORMATION_SCHEMA to find out.
通常我们会用一些关于它从哪里来的提示来命名它，它会在你的案例中引用它，fk_links_chatpicmessage_auth_user这样任何看到这个名字的人都会确切地知道这个约束是什么，而无需在 INFORMATION_SCHEMA 上进行复杂的查询来找出。
EDIT
编辑
As mentioned by @btubbs's answer you can actually add a column with a constraint in one command. Like so:
正如@btubbs 的回答所提到的，您实际上可以在一个命令中添加一个带有约束的列。像这样：
alter table links_chatpicmessage
add column sender integer,
add constraint fk_test
foreign key (sender)
references auth_user (id);
回答by btubbs
You can do this in Postgres on one line:
您可以在 Postgres 中的一行执行此操作：
ALTER TABLE links_chatpicmessage ADD COLUMN sender INTEGER REFERENCES auth_user (id);
You don't need to manually set a name. Postgres will automatically name this constraint "links_chatpicmessage_auth_user_id_fkey".
您无需手动设置名称。Postgres 会自动将此约束命名为“links_chatpicmessage_auth_user_id_fkey”。
回答by Ted Spradley
I know this answer is way late, and I realize this is the same as btubbs one-liner, just a little more descriptive ...
我知道这个答案来得太晚了，我意识到这与 bbbbs one-liner 相同，只是更具描述性...
Assuming you want to reference the primary key in table auth_user and that key name is 'id'.
假设您要引用表 auth_user 中的主键并且该键名称是“id”。
I use this syntax:
我使用这个语法：
ALTER TABLE links_chatpicmessage
ADD COLUMN sender some_type,
ADD FOREIGN KEY (sender) REFERENCES auth_user(id);
Note: some_type = [type the same as sender in table auth_user]
注意：some_type = [类型与表auth_user中的sender相同]
回答by Evan Carroll
The CONSTRAINTclause is optional. I suggest ommiting it and always letting PostgreSQL autoname the constraint, without naming it you'll get a logical name
该CONSTRAINT条款是可选的。我建议省略它并始终让 PostgreSQL 自动命名约束，而不命名它你会得到一个逻辑名称
"links_chatpicmessage_sender_fkey" FOREIGN KEY (sender) REFERENCES auth_user(id)
That's what you'll likely want to know if an INSERTor UPDATEfails due to a constraint violation.
如果INSERT或UPDATE因违反约束而失败，这就是您可能想知道的。
Syntax to add a foreign key
添加外键的语法
All of these are somewhat documented on ALTER TABLE
所有这些都有些记录在 ALTER TABLE
To a new column
到新列
ALTER TABLE links_chatpicmessage
ADD COLUMN sender int,
ADD [CONSTRAINT foo] FOREIGN KEY (sender) REFERENCES auth_user(id);
This is compound and transactional. You can issue two ALTERstatements on the same table by separating the two statements with a ,.
这是复合和交易性的。您可以ALTER在同一个表上发出两个语句，方法是用,.
To a preexisting column
到预先存在的列
-- assumes someone has already added the column or that it already exists
ALTER TABLE links_chatpicmessage
ADD COLUMN sender int;
ALTER TABLE links_chatpicmessage
ADD [CONSTRAINT foo] FOREIGN KEY (sender) REFERENCES auth_user(id);
回答by Jagadeesha N
****foreign key reference for existing column****
****现有列的外键引用****
ALTER TABLE table_name ADD CONSTRAINT fkey_name FOREIGN KEY (id) REFERENCES ref_table(id)
ALTER TABLE table_name ADD CONSTRAINT fkey_name FOREIGN KEY (id) REFERENCES ref_table(id)
