Home » SQL & PL/SQL » SQL & PL/SQL » ORA-02291
ORA-02291 [message #523398] Sat, 17 September 2011 13:12 Go to next message
musiqdefunk
Messages: 2
Registered: September 2011
Junior Member
Good Afternoon,

I am a Linux Administrator that is now involved in some DBA stuff at work.

I understand this error when I do an update in a child table the data must refer to something in the parent table. My question is that I have 500 updates at one time but the error doesn't tell me which line or update has the problem. IS there a script or statement which can tell me which update is missing a parent key. Instead of going one line at a time and comparing it to the parent key, thanks.
Re: ORA-02291 [message #523399 is a reply to message #523398] Sat, 17 September 2011 13:20 Go to previous messageGo to next message
BlackSwan
Messages: 22911
Registered: January 2009
Senior Member
It would be helpful if you followed Posting Guidelines - http://www.orafaq.com/forum/t/88153/0/

02291, 00000,"integrity constraint (%s.%s) 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.


I would have expected this during INSERT.
If this really occurs during UPDATE, then it is changing a child Primary Key.

Debugging depends upon actual code being run.
Procedure might stop at offending line.
Re: ORA-02291 [message #523401 is a reply to message #523399] Sat, 17 September 2011 13:33 Go to previous messageGo to next message
musiqdefunk
Messages: 2
Registered: September 2011
Junior Member
10.2.0
Red Hat 5.7

I apologize your right this is during an insert not an update...

[Updated on: Sat, 17 September 2011 13:34]

Report message to a moderator

Re: ORA-02291 [message #523403 is a reply to message #523401] Sat, 17 September 2011 14:11 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2049
Registered: January 2010
Senior Member
musiqdefunk wrote on Sat, 17 September 2011 14:33
I apologize your right this is during an insert not an update...


It doesn't matter much if it is insert or update - you can still use error logging:

EXECUTE DBMS_ERRLOG.CREATE_ERROR_LOG('YOUR_TABLE_NAME', 'ERROR_LOG_TABLE_NAME');
INSERT INTO YOUR_TABLE_NAME
.
.
.
LOG ERRORS INTO ERROR_LOG_TABLE_NAME('error_tag');


SY.
Re: ORA-02291 [message #523408 is a reply to message #523403] Sat, 17 September 2011 15:05 Go to previous message
BlackSwan
Messages: 22911
Registered: January 2009
Senior Member
One possibility is to change the table name used by in the INSERT statement to a new/different table_name &
a table without any FK constraint.
This would allow every INSERT to succeed into a "temporary" table.

SELECT KEY_COLUMN FROM NEW_TABLE
MINUS
SELECT PRIMARY_KEY_COL FROM PARENT_TABLE;
above should result in identifying the row(s) where parent record does not exist.
Previous Topic: MERGE TWO TABLE INTO ONE
Next Topic: help required
Goto Forum:
  


Current Time: Sun Oct 26 00:12:01 CDT 2014

Total time taken to generate the page: 0.08178 seconds