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