SQL 违反 - 未找到父密钥 02291. 00000 - “完整性约束
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4367047/
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
Violated - parent key not found 02291. 00000 - "integrity constraint
提问by Tony Andrews
Hi I am developing a database in Oracle SQL dev, that is trying to access foriegn keys from another table. I am currently working on the ItemOrdered table which I've created with the following CREATE statement
嗨，我正在 Oracle SQL dev 中开发一个数据库，它试图从另一个表访问外键。我目前正在使用以下 CREATE 语句创建的 ItemOrdered 表
CREATE TABLE ItemOrdered(OrderID varchar2(9) REFERENCES Ords(OrderID),
BeltID varchar2(9) REFERENCES BeltID(BeltID),
Quantity varchar(4) NOT NULL,
PRIMARY KEY(OrderID, BeltID))
As you can See I have the following foriegn keys Ords and BeltID.
如您所见，我有以下外键 Ords 和 BeltID。
Now when I try to run the following statement
现在当我尝试运行以下语句时
INSERT INTO ItemOrdered VALUES(401565981,234489212,'2')
It gives me the following error
它给了我以下错误
violated - parent key not found 02291. 00000 - "integrity constraint (%s.%s) violated - parent key not found"
违反 - 未找到父键 02291. 00000 - “违反完整性约束 (%s.%s) - 未找到父键”
I have provided my Ords CREATE statement if its needed
如果需要，我已经提供了我的 Ords CREATE 声明
CREATE TABLE Ords(OrderID varchar2(9) PRIMARY KEY,
CustomerID varchar(9) REFERENCES Customers(CustomerID),
Expected_Delivery_Date date DEFAULT sysdate NOT NULL,
Actual_Delivery_Date date DEFAULT sysdate NOT NULL,
Payment_Due_Date date DEFAULT sysdate NOT NULL,
Order_Date date DEFAULT sysdate NOT NULL, Price Varchar(10),
Order_Placed varchar2(1) CONSTRAINT OrderPlaced
CHECK(Order_Placed IN('Y','N')) NOT NULL,
Order_Confirmed varchar2(1)
CONSTRAINT Order_Confirmed CHECK(Order_Confirmed IN('Y','N')) NOT NULL,
Order_Completed varchar2(1) CONSTRAINT Order_Completed
CHECK(Order_Completed IN('Y','N')) NOT NULL)
And I have also provided my BeltID CREATE statement
我还提供了我的 BeltID CREATE 声明
CREATE TABLE BeltID(BeltID varchar2(9) PRIMARY KEY,
BeltLengthID varchar2(9) REFERENCES BeltLength(BeltLengthID),
ColourID varchar2(9) REFERENCES Colour(ColourID),
DesignID varchar2(9) REFERENCES Design(DesignID),ComponentID varchar2(9) REFERENCES Component(ComponentID))
I don't seem to quite understand why I am getting this error. Is there an clear explanation why?
我似乎不太明白为什么我会收到这个错误。有明确的解释为什么吗？
Here is the http link of what I am trying to do. link text
这是我正在尝试做的 http 链接。 链接文字
回答by Tony Andrews
Due to the foreign key constraints you specified when you created table ItemOrdered, when you perform this insert:
由于您在创建表 ItemOrdered 时指定的外键约束，当您执行此插入时：
INSERT INTO ItemOrdered VALUES(401565981,234489212,'2')
... the values 401565981 and 234489212 must correspond to key values in the Ords and BelitId tables respectively - i.e. these 2 queries should return rows:
...值 401565981 和 234489212 必须分别对应于 Ords 和 BelitId 表中的键值 - 即这两个查询应返回行：
select *
from Ords
where OrderId = 401565981;
select *
from BeltId
where BeltId = 234489212;
The error message suggests this is not the case.
错误消息表明情况并非如此。
回答by Ronnis
I have no Oracle installation available so I can't test, but does it matter if you enclose the OrderID and BeltID in single quotes (which you should do anyway since the columns are declared as varchars)? I haven't tested it, but one idea would be that Oracle barfs on the fact that you are inserting a numeric value into a varchar column.
我没有可用的 Oracle 安装，所以我无法测试，但是如果将 OrderID 和 BeltID 括在单引号中是否重要（因为列被声明为 varchars，所以无论如何都应该这样做）？我还没有测试过它，但一个想法是 Oracle 对您将数值插入到 varchar 列这一事实表示不满。
insert
into ItemOrdered VALUES('401565981', '234489212','2')
Another option would be that the order of the columns in the table does not correspond to the order you gave them in the insert statement. To rule this out, try rewrite the statement as:
另一种选择是表中列的顺序与您在插入语句中给出的顺序不一致。要排除这种情况，请尝试将语句重写为：
insert
into ItemOrdered(OrderID,BeltID,Quantity) values('401565981', '234489212','2')
