postgresql 当我尝试向 Postgres 中的现有表添加约束时，为什么会出现 SQL 状态：23503？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/19641739/
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
Why do I get SQL state:23503 when I try to add a constraint to an existing table in Postgres?
提问by Loolooii
I have table Advisorwhich is a special Userand contains only idand user_id(for now!) and I'm trying to make user_ida foreign key with the following script:
我有Advisor一个特殊的表，User它只包含id和user_id（现在！），我正在尝试user_id使用以下脚本制作外键：
ALTER TABLE advisor
ADD CONSTRAINT advisor_user_id_fkey
FOREIGN KEY (user_id) REFERENCES "user" (id);
Which I think should work, however I get this error:
我认为应该可行，但是我收到此错误：
ERROR: insert or update on table "advisor" violates foreign key constraint "advisor_user_id_fkey"
SQL state: 23503
Detail: Key (user_id)=(44) is not present in table "user".
I thinks this is weird because I'm saying it should refer to user.idand not user.user_id, but obviously I'm doing something wrong.
Does anyone have any idea about this? Thanks.
我认为这很奇怪，因为我说它应该指代user.id而不是user.user_id，但显然我做错了什么。有没有人对此有任何想法？谢谢。
update:if anyone is wondering why "user" and not user, well pgAdmin doesn't like user, because it thinks it's the owner of the database.
更新：如果有人想知道为什么“用户”而不是用户，那么 pgAdmin 不喜欢用户，因为它认为它是数据库的所有者。
回答by bma
"user" is a reserved word, that's why pgadmin is having trouble with it. If you have the ability to change that, I recommend it. See reserved words at: http://www.postgresql.org/docs/current/static/sql-keywords-appendix.html.
“用户”是一个保留字，这就是 pgadmin 遇到问题的原因。如果你有能力改变它，我推荐它。请参阅保留字：http: //www.postgresql.org/docs/current/static/sql-keywords-appendix.html。
Your error message clearly states that you have an entry in your "advisor" table (44) which does not exist in your "user" table. Your Foreign Key is defined on "advisor" and stipulates that the "user" table is the parent table. Perhaps you have that Foreign Key defined backwards?
您的错误消息清楚地表明您在“顾问”表 (44) 中有一个条目，而该条目在“用户”表中不存在。您的外键在“顾问”上定义，并规定“用户”表是父表。也许你有反向定义的外键？
