ORA-02292: integrity constraint (xxxx) violated - child record found
在更新表的主鍵欄位或DELETE資料時,如果遇到ORA-02292: integrity constraint (xxxx) violated - child record found 這個是因為主外來鍵關係,下面藉助一個小列子來描述一下這個錯誤:
實驗:
--建立主表
SQL> create table student(id number,name nvarchar2(12),constraint pk_student primary key(id));
Table created.
--建立外來鍵約束表
SQL> create table grades(id number ,subject nvarchar2(12),scores number,constraint pk_grades primary key(id ,subject),constraint fk_student_id foreign key(id) references student(id));
Table created.
SQL> insert into student values(1001,'kerry');
1 row created.
SQL> insert into student values(1002,'jimmy');
1 row created.
SQL> commit;
Commit complete.
SQL> insert into grades values(1001, 'math', 120);
1 row created.
SQL> insert into grades values(1001, 'english', 106);
1 row created.
SQL> commit;
Commit complete.
--檢視:
SQL> select * from student;
ID NAME
---------- ------------------------
1001 kerry
1002 jimmy
SQL> select * from grades;
ID SUBJECT SCORES
---------- ------------------------ ----------
1001 math 120
1001 english 106
--更新主表列
SQL> update student set id=1004 where name='kerry';
update student set id=1004 where name='kerry'
*
ERROR at line 1:
ORA-02292: integrity constraint (SYS.FK_STUDENT_ID) violated - child record
found
--報錯,解決:首先找到外來鍵約束和相關表,禁用外來鍵約束,處理資料,然後啟用外來鍵約束:查詢dba_constraints
SQL> col OWNER for a10
SQL> select owner,CONSTRAINT_NAME,TABLE_NAME,SEARCH_CONDITION,VALIDATED from dba_constraints where constraint_name='FK_STUDENT_ID';
OWNER CONSTRAINT_NAME TABLE_NAME SEARCH_CONDITION VALIDATED
---------- ------------------------------ ------------------------------ ------------------------------ -------------
SYS FK_STUDENT_ID GRADES VALIDATED
SQL>
--disable約束:
SQL> alter table sys.grades disable constraint FK_STUDENT_ID;
Table altered.
SQL>
SQL> update student set id=1004 where name='kerry';
1 row updated.
SQL> commit;
Commit complete.
SQL> update grades set id=1004 where id =1001;
2 rows updated.
SQL> commit;
Commit complete.
--修改完後,再enable約束:
SQL> alter table sys.grades enable constraint FK_STUDENT_ID;
Table altered.
--檢視:
SQL> select * from student;
ID NAME
---------- ------------------------
1004 kerry
1002 jimmy
SQL> select * from grades;
ID SUBJECT SCORES
---------- ------------------------ ----------
1004 math 120
1004 english 106
SQL>
---如果是刪除資料遇到這種情況,可以先刪除子表資料,然後刪除父表資料,如下:
SQL> delete from student where id=1004;
delete from student where id=1004
*
ERROR at line 1:
ORA-02292: integrity constraint (SYS.FK_STUDENT_ID) violated - child record found
--先刪除子表中相關列資料,
SQL> delete from grades where id in ( select id from student where id=1004);
2 rows deleted.
--再次刪除
SQL> delete from student where id=1004;
1 row deleted.
SQL> commit;
Commit complete.
SQL> select * from student;
ID NAME
---------- ------------------------
1002 jimmy
SQL> select * from grades;
no rows selected
SQL>
完結!!!!!!!!!!!!!!1
實驗:
--建立主表
SQL> create table student(id number,name nvarchar2(12),constraint pk_student primary key(id));
Table created.
--建立外來鍵約束表
SQL> create table grades(id number ,subject nvarchar2(12),scores number,constraint pk_grades primary key(id ,subject),constraint fk_student_id foreign key(id) references student(id));
Table created.
SQL> insert into student values(1001,'kerry');
1 row created.
SQL> insert into student values(1002,'jimmy');
1 row created.
SQL> commit;
Commit complete.
SQL> insert into grades values(1001, 'math', 120);
1 row created.
SQL> insert into grades values(1001, 'english', 106);
1 row created.
SQL> commit;
Commit complete.
--檢視:
SQL> select * from student;
ID NAME
---------- ------------------------
1001 kerry
1002 jimmy
SQL> select * from grades;
ID SUBJECT SCORES
---------- ------------------------ ----------
1001 math 120
1001 english 106
--更新主表列
SQL> update student set id=1004 where name='kerry';
update student set id=1004 where name='kerry'
*
ERROR at line 1:
ORA-02292: integrity constraint (SYS.FK_STUDENT_ID) violated - child record
found
--報錯,解決:首先找到外來鍵約束和相關表,禁用外來鍵約束,處理資料,然後啟用外來鍵約束:查詢dba_constraints
SQL> col OWNER for a10
SQL> select owner,CONSTRAINT_NAME,TABLE_NAME,SEARCH_CONDITION,VALIDATED from dba_constraints where constraint_name='FK_STUDENT_ID';
OWNER CONSTRAINT_NAME TABLE_NAME SEARCH_CONDITION VALIDATED
---------- ------------------------------ ------------------------------ ------------------------------ -------------
SYS FK_STUDENT_ID GRADES VALIDATED
SQL>
--disable約束:
SQL> alter table sys.grades disable constraint FK_STUDENT_ID;
Table altered.
SQL>
SQL> update student set id=1004 where name='kerry';
1 row updated.
SQL> commit;
Commit complete.
SQL> update grades set id=1004 where id =1001;
2 rows updated.
SQL> commit;
Commit complete.
--修改完後,再enable約束:
SQL> alter table sys.grades enable constraint FK_STUDENT_ID;
Table altered.
--檢視:
SQL> select * from student;
ID NAME
---------- ------------------------
1004 kerry
1002 jimmy
SQL> select * from grades;
ID SUBJECT SCORES
---------- ------------------------ ----------
1004 math 120
1004 english 106
SQL>
---如果是刪除資料遇到這種情況,可以先刪除子表資料,然後刪除父表資料,如下:
SQL> delete from student where id=1004;
delete from student where id=1004
*
ERROR at line 1:
ORA-02292: integrity constraint (SYS.FK_STUDENT_ID) violated - child record found
--先刪除子表中相關列資料,
SQL> delete from grades where id in ( select id from student where id=1004);
2 rows deleted.
--再次刪除
SQL> delete from student where id=1004;
1 row deleted.
SQL> commit;
Commit complete.
SQL> select * from student;
ID NAME
---------- ------------------------
1002 jimmy
SQL> select * from grades;
no rows selected
SQL>
完結!!!!!!!!!!!!!!1
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/31397003/viewspace-2146798/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- gorm忽略報錯: record not foundGoORM
- Default Value Insertion and Integrity Constraint Checking (164)AI
- ORA-00001 Unique Constraint SYS.I_JOB_JOB ViolatedAI
- ORA-00001 : Unique Constraint Violated caused by DATE VS Timestamp(6)AI
- 如何處理 No DMARC Record Found 問題
- ORA-00001: UNIQUE CONSTRAINT (SYS.WRI$_SQLSET_PLANS_PK) VIOLATEDAISQL
- 漏洞反饋,使用者授權報錯Integrity constraint violationAI
- 【YashanDB資料庫】自關聯外來鍵插入資料時報錯:YAS-02033 foreign key constraint violated parent key not found資料庫AI
- ORA-00001: unique constraint (...) violated並不一定是資料衝突AI
- ORA-00001: unique constraint (PERFSTAT.STATS$SQL_SUMMARY_PK) violated 解決方法AISQL
- new Child();new Child(1);
- Referential Integrity (324)
- Subresource Integrity 介紹
- 記解決OEM起不來提示C4J_DBConsole_xxxx(hostname)_xxxx(sid) not found
- flutter和Android混編下出現“No implementation found for method xxxx on channel”錯誤FlutterAndroid
- ConstraintAI
- CSS3偽類選擇器:nth-child()(nth-child(odd)/nth-child(even))CSSS3
- HV000030: No validator could be found for constraint ‘javax.validation.constraints.Pattern‘ validatiAIJava
- jQuery :only-childjQuery
- jQuery :nth-child()jQuery
- jQuery :first-childjQuery
- jQuery :last-childjQueryAST
- REFERENTIAL INTEGRITY AND LOCKING [ID 33453.1]
- react-recordReact
- 約束CONSTRAINTAI
- constraint約束AI
- Unused&ConstraintAI
- 淺析query_rewrite_integrity引數
- OCR integrity results are inconsistent amongst the nodes.
- CSS E:first-childCSS
- CSS E:last-childCSSAST
- CSS E:only-childCSS
- jQuery :nth-last-child()jQueryAST
- Daily record-SeptemberAI
- Deferred Constraint CheckAI
- constraint=constraints?AI
- SAP FI-AA Line item causes scrap value of to be violated
- oracle RAC Task resolv.conf IntegrityOracle