Wednesday, June 10, 2015

ORA-02292: integrity constraint (constraint_name) violated - child record found

I know this is very simple thing but lot of time I am facing this question from newbies that's why this thread.

Solution for this error,  you have to drop your current foreign key constraint and create new one with  one of below options

ON DELETE SET NULL
or
ON DELETE CASCADE

but before applying that you have to understand your business domain. because ON DELETE CASCADE  will delete the referencing rows from that child tables and ON DELETE SET NULL will set a null value on that referencing column's rows.

Just a simple practical example-

CREATE TABLE FAMILY (FAMILY_ID NUMBER);


CREATE TABLE MEMBER
(
   MEMBER_ID   NUMBER,
   FAMILY_ID   NUMBER
);


-- Primary Key Constraints for Table FAMILY

ALTER TABLE FAMILY ADD (
  CONSTRAINT FAMILY_PK
  PRIMARY KEY
  (FAMILY_ID)
  ENABLE VALIDATE);


-- Foreign Key Constraints for Table MEMBER


ALTER TABLE MEMBER ADD (
  CONSTRAINT MEMBER_FAMILY_FK
  FOREIGN KEY (FAMILY_ID)
  REFERENCES FAMILY (FAMILY_ID)
  ENABLE VALIDATE);

INSERT INTO FAMILY (FAMILY_ID)
     VALUES (1);

INSERT INTO FAMILY (FAMILY_ID)
     VALUES (2);

INSERT INTO FAMILY (FAMILY_ID)
     VALUES (3);

INSERT INTO FAMILY (FAMILY_ID)
     VALUES (4);

INSERT INTO FAMILY (FAMILY_ID)
     VALUES (5);

COMMIT;


INSERT INTO MEMBER (MEMBER_ID, FAMILY_ID)
     VALUES (1, 1);

INSERT INTO MEMBER (MEMBER_ID, FAMILY_ID)
     VALUES (1, 1);

INSERT INTO MEMBER (MEMBER_ID, FAMILY_ID)
     VALUES (1, 1);

INSERT INTO MEMBER (MEMBER_ID, FAMILY_ID)
     VALUES (1, 5);

INSERT INTO MEMBER (MEMBER_ID, FAMILY_ID)
     VALUES (1, 5);

INSERT INTO MEMBER (MEMBER_ID, FAMILY_ID)
     VALUES (1, 5);

INSERT INTO MEMBER (MEMBER_ID, FAMILY_ID)
     VALUES (1, 3);

INSERT INTO MEMBER (MEMBER_ID, FAMILY_ID)
     VALUES (1, 3);

INSERT INTO MEMBER (MEMBER_ID, FAMILY_ID)
     VALUES (1, 3);

INSERT INTO MEMBER (MEMBER_ID, FAMILY_ID)
     VALUES (1, 2);

COMMIT;



DELETE FROM family  WHERE family_id = 5               ---it will generate an error
                        
                        



     ORA-02292: integrity constraint (CRIC.MEMBER_FAMILY_FK) violated - child record found



Solution:-

1) drop foreign key (before drop please please backup the old script if your are not sure about relationship )


ALTER TABLE MEMBER
   DROP CONSTRAINTS MEMBER_FAMILY_FK


2) create like below


 ALTER TABLE MEMBER
 ADD (CONSTRAINT MEMBER_FAMILY_FK FOREIGN KEY (family_id)
 REFERENCES family (family_id)
 ON DELETE SET NULL ) ---- on delete cascade );



DELETE FROM family   WHERE family_id = 5  --in this time no error


rollback;

Cheers......Halim

2 comments:

Cat Minds said...

I was looking for the Oracle Online Training courses and your website really help me in finding my needs. This site contains all the stuff which i was looking . Thanks for this great work and i hope this will help a lots of users to achieve their goals.

John Nash said...

Thx a lot For sharing such a nice information on
Oracle 11g DBA Online Training