Home » SQL & PL/SQL » SQL & PL/SQL » ORA-02292: integrity constraint error (oracle 12c)
ORA-02292: integrity constraint error [message #648758] Wed, 02 March 2016 18:00 Go to next message
gorants
Messages: 85
Registered: May 2014
Location: ATL
Member
Hello all,

when i am trying to delete record from below KLMOP"."LOC" i got below error. Not sure what wrong here i am first deleting child record correct?

ORA-02292: integrity constraint FK_CUR_LOC violated - child record found



CREATE TABLE "NMKGL"."USER_LOC" 
   (	
    "ID" VARCHAR2(25 CHAR) NOT NULL ENABLE, 
	"LOC" NUMBER(9,0) NOT NULL ENABLE, 
	"DELETION" NUMBER(4,0) DEFAULT 0 NOT NULL ENABLE, 
	"PARENT_ID" NUMBER(9,0), 
	 CONSTRAINT "PK_USER_C_L" PRIMARY KEY ("ID", "LOC")
	 CONSTRAINT "FK_CUR_LOC" FOREIGN  KEY ("L_ID") REFERENCES "KLMOP"."LOC" ("L_ID") ENABLE
   ) 
   


Re: ORA-02292: integrity constraint error [message #648759 is a reply to message #648758] Wed, 02 March 2016 18:09 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
you are not allowed to delete a parent row that has existing child row.
Re: ORA-02292: integrity constraint error [message #648760 is a reply to message #648758] Wed, 02 March 2016 21:04 Go to previous message
Barbara Boehmer
Messages: 9088
Registered: November 2002
Location: California, USA
Senior Member
Your create table statement is invalid, since it does not even contain the column that the constraint is created on.

If you want to automatically delete all child records when you delete a parent record, then you can use ON DELETE CASCADE as shown below.

CONSTRAINT "FK_CUR_LOC" FOREIGN KEY ("L_ID") REFERENCES "KLMOP"."LOC" ("L_ID") ON DELETE CASCADE ENABLE
Previous Topic: "ORA-01722: invalid number" errors after upgrade 12c
Next Topic: end-of-file on communication channel
Goto Forum:
  


Current Time: Fri Apr 19 19:49:18 CDT 2024