oracle 错误报告：SQL 错误：外键值没有匹配的主键值。一头雾水
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15006528/
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
Error report: SQL Error: A foreign key value has no matching primary key value. Completely baffled
提问by bananabreadbob
I can safely say that I am new to SQL and therefore upon writing the code to insert data into a table, I received an error report, that I can't seem to understand what it means, therefore I am hoping someone out there may be able to tell me what silly mistake I am making and remove a lot of stress ^.^
我可以肯定地说我是 SQL 的新手，因此在编写将数据插入表中的代码时，我收到了一个错误报告，我似乎无法理解它的含义，因此我希望有人在那里能够告诉我我犯了什么愚蠢的错误并消除了很多压力^.^
This is the error code I got:
这是我得到的错误代码：
Error report: SQL Error: ORA-02291: integrity constraint (H.VENDOR_ID_FK) violated - parent key not found 02291. 00000 - "integrity constraint (%s.%s) violated - parent key not found" *Cause: A foreign key value has no matching primary key value. *Action: Delete the foreign key or add a matching primary key.
错误报告：SQL 错误：ORA-02291：违反完整性约束 (H.VENDOR_ID_FK) - 未找到父键 02291.00000 - “违反完整性约束 (%s.%s) - 未找到父键” *原因：外键value 没有匹配的主键值。*操作：删除外键或添加匹配的主键。
Thanks in advance!
提前致谢！
回答by Alex Poole
If I run all of that I get several errors before this point, which I'll skip over for now... the first one against this FK seems to be:
如果我运行所有这些，我会在此之前遇到几个错误，我现在将跳过这些错误……针对此 FK 的第一个错误似乎是：
INSERT INTO parts_order VALUES
(2
,2
,95115995
,'Delivered'
,'04/dec/2012'
,'01/jan/2013'
,'20/dec/2012'
,'Handler Pro'
);
It's better to put the columns in the insert clause so you can see what lines up (i.e. INSERT INTO parts_order (order_id, job_id, vendor_id, ...) VALUES (4, 4, 95115995, ...), and also so you (and we) don't have to refer back to the table definition, and to avoid failures if the definition changes in the future. It's also better not to rely on implicit date conversions (i.e. use to_char('05/jan/2013', 'DD/mon/YYYY').
最好将列放在 insert 子句中，这样您就可以看到对齐的内容（即INSERT INTO parts_order (order_id, job_id, vendor_id, ...) VALUES (4, 4, 95115995, ...)，并且您（和我们）不必再参考表定义，并避免在定义更改时失败未来。最好不要依赖隐式日期转换（即使用to_char('05/jan/2013', 'DD/mon/YYYY').
Anyway... the constraint it's complaining about is VENDOR_ID_FK, which we can see in the table definition:
无论如何......它抱怨的约束是VENDOR_ID_FK，我们可以在表定义中看到：
CREATE TABLE parts_order
( order_id NUMBER(11)
CONSTRAINT order_id_pk PRIMARY KEY
,job_id NUMBER(11)
CONSTRAINT job_id_fk REFERENCES maintenance(job_id)
,vendor_id NUMBER(11)
CONSTRAINT vendor_id_fk REFERENCES parts_vendor(part_vendor_id)
,parts_status VARCHAR2(20)
,date_ordered DATE
,date_arrived DATE
,date_delivery_due DATE
,part_name VARCHAR2(20)
CONSTRAINT part_name_nn NOT NULL);
... is against parts_vendor(part_vendor_id). What the error is saying is that the vendor_idyou're inserting, 95115995, doesn't exist in the parent parts_vendortable - which is true, you only insert records with part_vendor_idvalues 1, 2, 3and 4.
……反对parts_vendor(part_vendor_id)。哪些错误是说的是，vendor_id你要插入，95115995不父存在parts_vendor表-与是真实的，你只插入记录part_vendor_id值1，2，3和4。
The constraint is working as intended - it's stopping you putting a 'child' record in without its 'parent' existing. You either need to create a parts_vendorrecord for ID 95115995, or change the vendor_idvalue you're trying to insert into parts_orderto one that already exists.
该约束按预期工作 - 它阻止您在其“父”不存在的情况下放入“子”记录。您要么需要parts_vendor为 ID创建一条记录95115995，要么将vendor_id您尝试插入的值更改为parts_order已存在的值。
