Skip to Main Content
  • Questions

  • ORA-02292: integrity constraint with FK reference with same table PK

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Sam.

Asked: April 29, 2017 - 8:03 am UTC

Last updated: May 19, 2017 - 10:35 pm UTC

Version: 12.1.0.2

Viewed 1000+ times

You Asked

Hello Tom,

Need Ur help.

DB : 12.1.0.2.0

While execution script we are facing below issue:

-- Table have PK and FK (FK refer to same table PK).

While we execute script for delete some data , But some how we get the error of
ORA-02292: integrity constraint (CRESTELSYSTEMCRESTEL62308.FK_ACTION_ACTION_1) violated - child record found

This is the query:

delete from TBLMACTION WHERE SCREENID IN ( SELECT SCREENID FROM TBLSSCREEN where alias='PRODUCT_MANAGER')


This is the Table structure:

CREATE TABLE "TBLMACTION"
( "ACTIONID" CHAR(8 BYTE),
"NAME" VARCHAR2(100 BYTE),
"SCREENID" CHAR(6 BYTE),
"ALIAS" VARCHAR2(50 BYTE),
"DESCRIPTION" VARCHAR2(255 BYTE),
"ACTIONTYPEID" CHAR(5 BYTE),
"PARENTACTIONID" CHAR(8 BYTE),
"ACTIONLEVEL" NUMBER(2,0),
"SYSTEMGENERATED" CHAR(1 BYTE),
"MODULE" VARCHAR2(100 BYTE),
"LINK" VARCHAR2(255 BYTE),
"ACTIONCLASS" VARCHAR2(200 BYTE),
"DISPLAYORDER" NUMBER(*,0) DEFAULT 0,
"HYPERLINKTYPE" VARCHAR2(20 BYTE) DEFAULT 'FIXED',
"AVPAIR" VARCHAR2(100 BYTE),
CONSTRAINT "NN_ANID_AN" CHECK (ACTIONID IS NOT NULL) ENABLE,
CONSTRAINT "NN_NAME_AN" CHECK (NAME IS NOT NULL) ENABLE,
CONSTRAINT "NN_SCREENID_AN" CHECK (SCREENID IS NOT NULL) ENABLE,
CONSTRAINT "NN_ALIAS_AN" CHECK (ALIAS IS NOT NULL) ENABLE,
CONSTRAINT "NN_ANTYPEID_AN" CHECK (ACTIONTYPEID IS NOT NULL) ENABLE,
CONSTRAINT "NN_ANLEVEL_AN" CHECK (ACTIONLEVEL IS NOT NULL) ENABLE,
CONSTRAINT "NN_SYGN_AN" CHECK (SYSTEMGENERATED IS NOT NULL) ENABLE,
CONSTRAINT "NN_MOD_AN" CHECK (MODULE IS NOT NULL) ENABLE,
CONSTRAINT "NN_DISPLAYORDER_AN" CHECK (DISPLAYORDER IS NOT NULL) ENABLE,
CONSTRAINT "PK_ACTION" PRIMARY KEY ("ACTIONID"),
CONSTRAINT "FK_ACTION_ACTION_1" FOREIGN KEY ("PARENTACTIONID")
REFERENCES "TBLMACTION" ("ACTIONID") ENABLE
) ;

We check data and all things,
Actually, Main Problem is that, Same script is successfully execute with oracle version 11.2.0.3 without any changes, But some how it is not execute with version 12.1.0.2.

Please let me know is there any related changes or BUG with 12c. Or I have to configure any parameter for same.



and Connor said...

Can you give me a test case - I can't reproduce on my instance

SQL> create table t ( x int primary key, y int, z int );

Table created.

SQL> insert into t values (1,10,2);

1 row created.

SQL> insert into t values (2,20,1);

1 row created.

SQL> insert into t values (3,30,7);

1 row created.

SQL> insert into t values (4,40,5);

1 row created.

SQL> insert into t values (5,50,6);

1 row created.

SQL> insert into t values (6,60,4);

1 row created.

SQL> insert into t values (7,70,2);

1 row created.

SQL> alter table t add constraint FK foreign key (z) references t ( x ) ;

Table altered.

SQL> --
SQL> -- z=7, points to x=3, no children, should be ok
SQL> --
SQL> delete from t where z = 7;

1 row deleted.

SQL> --
SQL> -- z=2, points to x=1,7, has other children, should fail
SQL> --
SQL> delete from t where z = 2;
delete from t where z = 2
*
ERROR at line 1:
ORA-02292: integrity constraint (MCDONAC.FK) violated - child record found


Rating

  (2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

test case for same

Sam patel, May 01, 2017 - 9:53 am UTC

Tom, Thanks for response,
We have .sql script and it execute sequencially.
Share with you table data.

Still main magic is that same script with same execution approach work fine with 11g but not in 12c.

Insert into TBLSSCREEN (SCREENID,NAME,ALIAS,DESCRIPTION,SYSTEMGENERATED) values ('SCR003','Product Manager','PRODUCT_MANAGER','Product Manager','Y');


Insert into TBLMACTION (ACTIONID,NAME,SCREENID,ALIAS,DESCRIPTION,ACTIONTYPEID,PARENTACTIONID,ACTIONLEVEL,SYSTEMGENERATED,MODULE,LINK,ACTIONCLASS,DISPLAYORDER,HYPERLINKTYPE,AVPAIR) values ('ACN03317','Create Product Package','SCR003','CREATE_SERVICE_PACKAGE_ACTION','Create Product Package','ACT02','ACN03315',2,'N','MOD593',null,'com.elitecore.billing.operationsmanager.servicepackage.InitCreateServicePackageAction',2,'FIXED',null);


Insert into TBLMACTION (ACTIONID,NAME,SCREENID,ALIAS,DESCRIPTION,ACTIONTYPEID,PARENTACTIONID,ACTIONLEVEL,SYSTEMGENERATED,MODULE,LINK,ACTIONCLASS,DISPLAYORDER,HYPERLINKTYPE,AVPAIR) values ('ACN03316','Product Package','SCR003','SEARCH_SERVICE_PACKAGE_ACTION','Search Product Package','ACT06','ACN03315',2,'N','MOD593','/zul/product/offerspecification/InitSearchPackage.zul',null,1,'CUSTOM',null);

Insert into TBLMACTION (ACTIONID,NAME,SCREENID,ALIAS,DESCRIPTION,ACTIONTYPEID,PARENTACTIONID,ACTIONLEVEL,SYSTEMGENERATED,MODULE,LINK,ACTIONCLASS,DISPLAYORDER,HYPERLINKTYPE,AVPAIR) values ('ACN02042','Download Content','SCR003','DOWNLOAD_CONTENT_ACTION','Download Content Action','ACT02',null,2,'N','MOD599',null,null,0,'FIXED',null);

Insert into TBLMACTION (ACTIONID,NAME,SCREENID,ALIAS,DESCRIPTION,ACTIONTYPEID,PARENTACTIONID,ACTIONLEVEL,SYSTEMGENERATED,MODULE,LINK,ACTIONCLASS,DISPLAYORDER,HYPERLINKTYPE,AVPAIR) values ('ACN02043','Upload Content','SCR003','UPLOAD_CONTENT_ACTION','Upload Content Action','ACT02',null,2,'N','MOD599',null,null,0,'FIXED',null);


Insert into TBLMACTION (ACTIONID,NAME,SCREENID,ALIAS,DESCRIPTION,ACTIONTYPEID,PARENTACTIONID,ACTIONLEVEL,SYSTEMGENERATED,MODULE,LINK,ACTIONCLASS,DISPLAYORDER,HYPERLINKTYPE,AVPAIR) values ('ACN03304','Rate Card Group','SCR003','SEARCH_RATE_CARD_GROUP_ACTION','Search Ratecard Group','ACT06','ACN03302',2,'N','MOD592','/zul/product/ratecardgroup/InitSearchRateCardGroup.zul',null,2,'CUSTOM',null);

Insert into TBLMACTION (ACTIONID,NAME,SCREENID,ALIAS,DESCRIPTION,ACTIONTYPEID,PARENTACTIONID,ACTIONLEVEL,SYSTEMGENERATED,MODULE,LINK,ACTIONCLASS,DISPLAYORDER,HYPERLINKTYPE,AVPAIR) values ('ACN03314','Download Invalid Lookup Policy CSV','SCR003','DOWNLOAD_INVALID_MDT_RECORDS_ACTION','Download Invalid Lookup Policy CSV','ACT02',null,2,'N','MOD592',null,null,0,'FIXED',null);

Insert into TBLMACTION (ACTIONID,NAME,SCREENID,ALIAS,DESCRIPTION,ACTIONTYPEID,PARENTACTIONID,ACTIONLEVEL,SYSTEMGENERATED,MODULE,LINK,ACTIONCLASS,DISPLAYORDER,HYPERLINKTYPE,AVPAIR) values ('ACN03315','Product Offer','SCR003','PRODUCT_MANAGER','Product Offer','ACT01','ACN03294',1,'Y','MOD593',null,null,6,null,null);

Insert into TBLMACTION (ACTIONID,NAME,SCREENID,ALIAS,DESCRIPTION,ACTIONTYPEID,PARENTACTIONID,ACTIONLEVEL,SYSTEMGENERATED,MODULE,LINK,ACTIONCLASS,DISPLAYORDER,HYPERLINKTYPE,AVPAIR) values ('ACN03316','Product Package','SCR003','SEARCH_SERVICE_PACKAGE_ACTION','Search Product Package','ACT06','ACN03315',2,'N','MOD593','/zul/product/offerspecification/InitSearchPackage.zul',null,1,'CUSTOM',null);



delete from TBLMACTION WHERE SCREENID IN ( SELECT SCREENID FROM TBLSSCREEN where alias='PRODUCT_MANAGER') ;

delete from tblsscreen where alias='PRODUCT_MANAGER';
Connor McDonald
May 04, 2017 - 3:06 am UTC

That data seems incomplete


SQL> CREATE TABLE "TBLMACTION"
  2  ( "ACTIONID" CHAR(8 BYTE),
  3  "NAME" VARCHAR2(100 BYTE),
  4  "SCREENID" CHAR(6 BYTE),
  5  "ALIAS" VARCHAR2(50 BYTE),
  6  "DESCRIPTION" VARCHAR2(255 BYTE),
  7  "ACTIONTYPEID" CHAR(5 BYTE),
  8  "PARENTACTIONID" CHAR(8 BYTE),
  9  "ACTIONLEVEL" NUMBER(2,0),
 10  "SYSTEMGENERATED" CHAR(1 BYTE),
 11  "MODULE" VARCHAR2(100 BYTE),
 12  "LINK" VARCHAR2(255 BYTE),
 13  "ACTIONCLASS" VARCHAR2(200 BYTE),
 14  "DISPLAYORDER" NUMBER(*,0) DEFAULT 0,
 15  "HYPERLINKTYPE" VARCHAR2(20 BYTE) DEFAULT 'FIXED',
 16  "AVPAIR" VARCHAR2(100 BYTE),
 17  CONSTRAINT "NN_ANID_AN" CHECK (ACTIONID IS NOT NULL) ENABLE,
 18  CONSTRAINT "NN_NAME_AN" CHECK (NAME IS NOT NULL) ENABLE,
 19  CONSTRAINT "NN_SCREENID_AN" CHECK (SCREENID IS NOT NULL) ENABLE,
 20  CONSTRAINT "NN_ALIAS_AN" CHECK (ALIAS IS NOT NULL) ENABLE,
 21  CONSTRAINT "NN_ANTYPEID_AN" CHECK (ACTIONTYPEID IS NOT NULL) ENABLE,
 22  CONSTRAINT "NN_ANLEVEL_AN" CHECK (ACTIONLEVEL IS NOT NULL) ENABLE,
 23  CONSTRAINT "NN_SYGN_AN" CHECK (SYSTEMGENERATED IS NOT NULL) ENABLE,
 24  CONSTRAINT "NN_MOD_AN" CHECK (MODULE IS NOT NULL) ENABLE,
 25  CONSTRAINT "NN_DISPLAYORDER_AN" CHECK (DISPLAYORDER IS NOT NULL) ENABLE,
 26  CONSTRAINT "PK_ACTION" PRIMARY KEY ("ACTIONID"),
 27  CONSTRAINT "FK_ACTION_ACTION_1" FOREIGN KEY ("PARENTACTIONID")
 28  REFERENCES "TBLMACTION" ("ACTIONID") ENABLE
 29  ) ;

Table created.

SQL>
SQL> create table TBLSSCREEN (SCREENID varchar2(30),
  2                 NAME varchar2(30),
  3                 ALIAS varchar2(30),
  4                 DESCRIPTION varchar2(30),
  5                 SYSTEMGENERATED varchar2(30));

Table created.

SQL> Insert into TBLSSCREEN (SCREENID,NAME,ALIAS,DESCRIPTION,SYSTEMGENERATED) values ('SCR003','Product Manager','PRODUCT_MANAGER','Product Manager','Y');

1 row created.

SQL>
SQL> alter table TBLMACTION modify constraint FK_ACTION_ACTION_1 disable;

Table altered.

SQL>
SQL> Insert into TBLMACTION (ACTIONID,NAME,SCREENID,ALIAS,DESCRIPTION,ACTIONTYPEID,PARENTACTIONID,ACTIONLEVEL,SYSTEMGENERATED,MODULE,LINK,ACTIONCLASS,DISPLAYORDER,HYPERLINKTYPE,AVPAIR) values ('ACN03317','Cr
eate Product Package','SCR003','CREATE_SERVICE_PACKAGE_ACTION','Create Product Package','ACT02','ACN03315',2,'N','MOD593',null,'com.elitecore.billing.operationsmanager.servicepackage.InitCreateServicePackage
Action',2,'FIXED',null);

1 row created.

SQL> Insert into TBLMACTION (ACTIONID,NAME,SCREENID,ALIAS,DESCRIPTION,ACTIONTYPEID,PARENTACTIONID,ACTIONLEVEL,SYSTEMGENERATED,MODULE,LINK,ACTIONCLASS,DISPLAYORDER,HYPERLINKTYPE,AVPAIR) values ('ACN03316','Pr
oduct Package','SCR003','SEARCH_SERVICE_PACKAGE_ACTION','Search Product Package','ACT06','ACN03315',2,'N','MOD593','/zul/product/offerspecification/InitSearchPackage.zul',null,1,'CUSTOM',null);

1 row created.

SQL> Insert into TBLMACTION (ACTIONID,NAME,SCREENID,ALIAS,DESCRIPTION,ACTIONTYPEID,PARENTACTIONID,ACTIONLEVEL,SYSTEMGENERATED,MODULE,LINK,ACTIONCLASS,DISPLAYORDER,HYPERLINKTYPE,AVPAIR) values ('ACN02042','Do
wnload Content','SCR003','DOWNLOAD_CONTENT_ACTION','Download Content Action','ACT02',null,2,'N','MOD599',null,null,0,'FIXED',null);

1 row created.

SQL> Insert into TBLMACTION (ACTIONID,NAME,SCREENID,ALIAS,DESCRIPTION,ACTIONTYPEID,PARENTACTIONID,ACTIONLEVEL,SYSTEMGENERATED,MODULE,LINK,ACTIONCLASS,DISPLAYORDER,HYPERLINKTYPE,AVPAIR) values ('ACN02043','Up
load Content','SCR003','UPLOAD_CONTENT_ACTION','Upload Content Action','ACT02',null,2,'N','MOD599',null,null,0,'FIXED',null);

1 row created.

SQL> Insert into TBLMACTION (ACTIONID,NAME,SCREENID,ALIAS,DESCRIPTION,ACTIONTYPEID,PARENTACTIONID,ACTIONLEVEL,SYSTEMGENERATED,MODULE,LINK,ACTIONCLASS,DISPLAYORDER,HYPERLINKTYPE,AVPAIR) values ('ACN03304','Ra
te Card Group','SCR003','SEARCH_RATE_CARD_GROUP_ACTION','Search Ratecard Group','ACT06','ACN03302',2,'N','MOD592','/zul/product/ratecardgroup/InitSearchRateCardGroup.zul',null,2,'CUSTOM',null);

1 row created.

SQL> Insert into TBLMACTION (ACTIONID,NAME,SCREENID,ALIAS,DESCRIPTION,ACTIONTYPEID,PARENTACTIONID,ACTIONLEVEL,SYSTEMGENERATED,MODULE,LINK,ACTIONCLASS,DISPLAYORDER,HYPERLINKTYPE,AVPAIR) values ('ACN03314','Do
wnload Invalid Lookup Policy CSV','SCR003','DOWNLOAD_INVALID_MDT_RECORDS_ACTION','Download Invalid Lookup Policy CSV','ACT02',null,2,'N','MOD592',null,null,0,'FIXED',null);

1 row created.

SQL> Insert into TBLMACTION (ACTIONID,NAME,SCREENID,ALIAS,DESCRIPTION,ACTIONTYPEID,PARENTACTIONID,ACTIONLEVEL,SYSTEMGENERATED,MODULE,LINK,ACTIONCLASS,DISPLAYORDER,HYPERLINKTYPE,AVPAIR) values ('ACN03315','Pr
oduct Offer','SCR003','PRODUCT_MANAGER','Product Offer','ACT01','ACN03294',1,'Y','MOD593',null,null,6,null,null);

1 row created.

SQL> --Insert into TBLMACTION (ACTIONID,NAME,SCREENID,ALIAS,DESCRIPTION,ACTIONTYPEID,PARENTACTIONID,ACTIONLEVEL,SYSTEMGENERATED,MODULE,LINK,ACTIONCLASS,DISPLAYORDER,HYPERLINKTYPE,AVPAIR) values ('ACN03316','
Product Package','SCR003','SEARCH_SERVICE_PACKAGE_ACTION','Search Product Package','ACT06','ACN03315',2,'N','MOD593','/zul/product/offerspecification/InitSearchPackage.zul',null,1,'CUSTOM',null);
SQL> alter table TBLMACTION modify constraint FK_ACTION_ACTION_1 enable;
alter table TBLMACTION modify constraint FK_ACTION_ACTION_1 enable
                                         *
ERROR at line 1:
ORA-02298: cannot validate (MCDONAC.FK_ACTION_ACTION_1) - parent keys not found


SQL>


Other relational records

Sam patel, May 09, 2017 - 10:20 am UTC

Yes Tom,

Its becuase parant value is not available in script.

Here is some more scipt. You can consider like that and also add other rows for your testing which record having actionid and parentactionid relation.

Again I run script , still it is mystry in 12c , getting constraints error.


Insert into TBLMACTION (ACTIONID,NAME,SCREENID,ALIAS,DESCRIPTION,ACTIONTYPEID,PARENTACTIONID,
ACTIONLEVEL,SYSTEMGENERATED,MODULE,LINK,ACTIONCLASS,DISPLAYORDER,HYPERLINKTYPE,AVPAIR) values
('ACN03294','Master Product Menu','SCR003','MASTER_PRODUCT_MENU','Master Menu','ACT07',null,1,'Y',
'MOD000',null,null,0,null,null);

Insert into TBLMACTION (ACTIONID,NAME,SCREENID,ALIAS,DESCRIPTION,ACTIONTYPEID,PARENTACTIONID,
ACTIONLEVEL,SYSTEMGENERATED,MODULE,LINK,ACTIONCLASS,DISPLAYORDER,HYPERLINKTYPE,AVPAIR) values
('ACN03315','Product Offer','SCR003','PRODUCT_MANAGER','Product Offer','ACT01','ACN03294',1,'Y',
'MOD593',null,null,6,null,null);


Insert into TBLMACTION (ACTIONID,NAME,SCREENID,ALIAS,DESCRIPTION,ACTIONTYPEID,PARENTACTIONID,ACTIONLEVEL,SYSTEMGENERATED,MODULE,LINK,ACTIONCLASS,DISPLAYORDER,HYPERLINKTYPE,AVPAIR) values ('ACN03316','Product Package','SCR003','SEARCH_SERVICE_PACKAGE_ACTION','Search Product Package','ACT06','ACN03315',2,'N','MOD593','/zul/product/offerspecification/InitSearchPackage.zul',null,1,'CUSTOM',null);

Insert into TBLMACTION (ACTIONID,NAME,SCREENID,ALIAS,DESCRIPTION,ACTIONTYPEID,PARENTACTIONID,ACTIONLEVEL,SYSTEMGENERATED,MODULE,LINK,ACTIONCLASS,DISPLAYORDER,HYPERLINKTYPE,AVPAIR) values ('ACN03317','Create Product Package','SCR003','CREATE_SERVICE_PACKAGE_ACTION','Create Product Package','ACT02','ACN03315',2,'N','MOD593',null,'com.elitecore.billing.operationsmanager.servicepackage.InitCreateServicePackageAction',2,'FIXED',null);

Insert into TBLMACTION (ACTIONID,NAME,SCREENID,ALIAS,DESCRIPTION,ACTIONTYPEID,PARENTACTIONID,ACTIONLEVEL,SYSTEMGENERATED,MODULE,LINK,ACTIONCLASS,DISPLAYORDER,HYPERLINKTYPE,AVPAIR) values ('ACN03578','Bundle Package','SCR003','PM_INIT_SEARCH_BUNDLE_PACKAGE_COMPOSER','BUNDLE PACKAGE','ACT06','ACN03315',2,'Y','MOD593','/zul/product/bundlepackage/InitSearchBundlePackage.zul',null,3,'CUSTOM',null);

Insert into TBLMACTION (ACTIONID,NAME,SCREENID,ALIAS,DESCRIPTION,ACTIONTYPEID,PARENTACTIONID,ACTIONLEVEL,SYSTEMGENERATED,MODULE,LINK,ACTIONCLASS,DISPLAYORDER,HYPERLINKTYPE,AVPAIR) values ('ACN03471','INIT CREATE RECHARGE PACKAGE ACTION','SCR003','INIT_PM_CREATE_RECHARGE_PACKAGE_ACTION','INIT CREATE RECHARGE PACKAGE ACTION','ACT02','ACN03315',2,'N','MOD593',null,null,0,'FIXED',null);



Connor McDonald
May 19, 2017 - 10:35 pm UTC

Give us a top to bottom test case that we can cut/paste and see the error.
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.