DATA Step, Macro, Functions and more

ORA-02291: integrity constraint (ECMUSER.INCUDFNUMVAL_FK1) violated - parent key not found.

Reply
Contributor
Posts: 21

ORA-02291: integrity constraint (ECMUSER.INCUDFNUMVAL_FK1) violated - parent key not found.

Here, i know child record is there but parent record not found. now i need to know how to find the child record that do not have parent. 

 


NOTE: Appending WORK.TEMP_MAP to ECM_DB.INCIDENT_UDF_NUM_VALUE.
NOTE: There were 1000 observations read from the data set WORK.TEMP_MAP.
NOTE: 999 observations added.
NOTE: The data set ECM_DB.INCIDENT_UDF_NUM_VALUE has . observations and 6 variables.
ERROR: ERROR: ERROR: ORACLE execute error: ORA-02291: integrity constraint (ECMUSER.INCUDFNUMVAL_FK1) violated - parent key not
found. With the occurrence of the above ERROR, the error limit of 1 set by the ERRLIMIT= option has been reached.
ROLLBACK has been issued(Any Rows processed after the last COMMIT are lost).

Total rows processed: 1
Rows failed : 1
NOTE: Statements not processed because of errors noted above.
NOTE: PROCEDURE APPEND used (Total process time):
real time 0.03 seconds
cpu time 0.01 seconds

NOTE: The SAS System stopped processing this step because of errors.
VERSION 6.3M1
ERROR: FCF: IEM: Unable to append to INCIDENT_UDF_NUM_VALUE
1328
1329
1330
1331 GOPTIONS NOACCESSIBLE;
1332 %LET _CLIENTTASKLABEL=;
1333 %LET _CLIENTPROCESSFLOWNAME=;
1334 %LET _CLIENTPROJECTPATH=;
1335 %LET _CLIENTPROJECTNAME=;
1336 %LET _SASPROGRAMFILE=;
1337
1338 ;*';*";*/;quit;run;
1339 ODS _ALL_ CLOSE;

Super User
Posts: 10,237

Re: ORA-02291: integrity constraint (ECMUSER.INCUDFNUMVAL_FK1) violated - parent key not found.

Posted in reply to kuppusamy

Do a join with the "parent" table:

proc sql;
create table test as
select a.* from
work.temp_map a left join ecm_db.parent b
on a.key = b.key
where b.key is missing;
quit;

Where "parent" is the reference table and "key" is your unique key for the violated constraint.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Contributor
Posts: 21

Re: ORA-02291: integrity constraint (ECMUSER.INCUDFNUMVAL_FK1) violated - parent key not found.

Posted in reply to KurtBremser

The dataset test getting all records. .

 

Super User
Posts: 10,237

Re: ORA-02291: integrity constraint (ECMUSER.INCUDFNUMVAL_FK1) violated - parent key not found.

Posted in reply to kuppusamy

@kuppusamy wrote:

The dataset test getting all records. .

 


Then I suggest you get in touch with your Oracle database admins/designers and have them check your data and code against their constraints.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Contributor
Posts: 21

Re: ORA-02291: integrity constraint (ECMUSER.INCUDFNUMVAL_FK1) violated - parent key not found.

Posted in reply to KurtBremser

Thank you for your response,

 

 

that error is because of risk assessment process(its completed with wrong input). now i rectified that and process running smoothly...

Ask a Question
Discussion stats
  • 4 replies
  • 55 views
  • 0 likes
  • 2 in conversation