SQL 错误：ORA-02291：完整性约束
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4349864/
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
SQL Error: ORA-02291: integrity constraint
提问by David
I am creating a database that is trying to access values from a foreign key. I have created two following tables
我正在创建一个试图从外键访问值的数据库。我创建了以下两个表
CREATE TABLE Component(
ComponentID varchar2(9) PRIMARY KEY
, TypeID varchar2(9) REFERENCES TypeComponent(TypeComponentID)
)
INSERT INTO Component VALUES(192359823,785404309)
INSERT INTO Component VALUES(192359347,785404574)
INSERT INTO Component VALUES(192359467,785404769)
INSERT INTO Component VALUES(192359845,785404867)
INSERT INTO Component VALUES(192359303,785404201)
INSERT INTO Component VALUES(192359942,785404675)
CREATE TABLE TypeComponent (
TypeComponentID varchar2(9) PRIMARY KEY
, Type_Description varchar2(30) CONSTRAINT Type_Description
CHECK(Type_Description IN('Strap', 'Buckle', 'Stud')) NOT NULL
)
INSERT INTO TypeComponent VALUES(785404309, 'Strap')
INSERT INTO TypeComponent VALUES(785404574, 'Stud')
INSERT INTO TypeComponent VALUES(785404769, 'Buckle')
INSERT INTO TypeComponent VALUES(785404867, 'Strap')
INSERT INTO TypeComponent VALUES(785404201, 'Buckle')
INSERT INTO TypeComponent VALUES(785404675, 'Stud')
These are the two tables. Componentand TypeComponent. Componentis the parent entity to TypeComponent, and I am trying to run the following INSERT statement:
这是两张桌子。Component和TypeComponent。Component是 的父实体TypeComponent，我正在尝试运行以下 INSERT 语句：
INSERT INTO Component VALUES(192359823,785404309)
but it is giving me the error
但它给了我错误
This is the session that I have so far in Oracle SQL dev
这是迄今为止我在 Oracle SQL dev 中的会话
采纳答案by Will Marcouiller
Try inserting in your TypeComponenttable first, then insert into your Componenttable.
尝试先插入TypeComponent表中，然后再插入Component表中。
According to the error:
根据错误：
ORA-02291: integrity constraint (string.string) 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.
ORA-02291： 违反完整性约束（string.string） - 未找到父键
原因： 外键值没有匹配的主键值。
行动： 删除外键或添加一个匹配的主键。
This means that there is no matching key in your referenced table.
这意味着您引用的表中没有匹配的键。
EDIT #1
编辑#1
For your kind information, here is a site where you can get help with all of the Oracle error codes.
对于您的信息，这里是一个站点，您可以在其中获得有关所有 Oracle 错误代码的帮助。
http://[ora-02291].ora-code.com/
http://[ora-02291].ora-code.com/
Here's the homepage for this site: http://www.ora-code.com/
这是该站点的主页：http: //www.ora-code.com/
Where you may replace the error code in the URL to suit the error code you're getting, and you'll access to the page for this error.
您可以在哪里替换 URL 中的错误代码以适合您得到的错误代码，并且您将访问此错误的页面。
回答by Rajesh Chamarthi
Please post your entire SQLPLUS session so that the error is easily reproducible.
请发布您的整个 SQLPLUS 会话，以便该错误很容易重现。
Looks like the insert into the child table is being done before the insert into the parent table which is causing this error.
看起来插入子表是在插入导致此错误的父表之前完成的。
Change the order of inserts and rerun your code.
更改插入顺序并重新运行代码。
SQL> CREATE TABLE TypeComponent(
2 TypeComponentID varchar2(9) PRIMARY KEY,
3 Type_Description varchar2(30)
4 CONSTRAINT Type_Description CHECK(Type_Description IN('Strap', 'Buckle', 'Stud')) NOT NULL
5 )
6 ;
Table created.
SQL> CREATE TABLE Component(
2 ComponentID varchar2(9) PRIMARY KEY,
3 TypeID varchar2(9) REFERENCES TypeComponent(TypeComponentID)
4 )
5 ;
Table created.
SQL> INSERT INTO Component VALUES(192359823,785404309);
INSERT INTO Component VALUES(192359823,785404309)
*
ERROR at line 1:
ORA-02291: integrity constraint (COAMGR.SYS_C002513823) violated - parent key
not found
SQL> INSERT INTO TypeComponent VALUES(785404309, 'Strap');
1 row created.
SQL> INSERT INTO Component VALUES(192359823,785404309);
1 row created.
SQL> commit;
Commit complete.
回答by Sathyajith Bhat
In componenttable you have
在component表中你有
TypeID REFERENCES TypeComponent(TypeComponentID)
And then you proceed with
然后你继续
INSERT INTO Component VALUES(192359823,785404309)
If you executed this first, then Oracle will throw Integrity constrainterror because you are trying to insert into componenttable a value which is not present in TypeComponenttable.
如果您先执行此操作，那么 Oracle 将抛出Integrity constraint错误，因为您试图将component表中不存在的值插入TypeComponent表中。
This is a problem because you have mentioned that TypeIDis a foreign key, ie, the value of TypeIDneeds to be present in TypeComponentbefore inserting into Component
这是一个问题，因为您已经提到这TypeID是一个外键，即在插入之前TypeID需要存在的值TypeComponentComponent
回答by sergio pachon
This error is present some times with the referenced key (foreign key in my case) this have errors. Please recreate the referenced key in the table.
此错误有时会与引用的键（在我的情况下为外键）一起出现，这有错误。请在表中重新创建引用的键。
