Foreign Keys with ON DELETE CASCADE option

Ij
Posted by Ij under Oracle category on | Points: 40 | Views : 345
Foreign Keys with ON DELETE CASCADE option allows us to create parent child tables which automatically deletes referenced data in child tables when deleted from parent table.
Normally when we try to delete a row from a parent table which is having related record in a child table,oracle will not allow to delete the row from parent table.Oracle will throw ORA-02292: integrity constraint violated – child record found error.
To avoid this difficulty we can add ‘ON DELETE CASCADE‘ statement during the creation of Child table foreign keys.
create table student(
name VARCHAR2(50),
s_id NUMBER,
fee NUMBER(8,2),
CONSTRAINT SSS_ID_PK PRIMARY KEY (s_id));

INSERT INTO STUDENT VALUES('RAJESH',1,10000);
INSERT INTO STUDENT VALUES('MAHESH',2,20000);
INSERT INTO STUDENT VALUES('SURESH',3,30000);

SELECT * FROM STUDENT;

CREATE TABLE STUDENT_ADDRESS(ADDRESS VARCHAR2(10),S_ID NUMBER,CONSTRAINT SS_ID_FK FOREIGN KEY(S_ID) REFERENCES STUDENT(S_ID));

INSERT INTO STUDENT_ADDRESS VALUES('JMD',1);
INSERT INTO STUDENT_ADDRESS VALUES('BGLR',2);
INSERT INTO STUDENT_ADDRESS VALUES('DELHI',3);

SELECT * FROM STUDENT_ADDRESS;

DELETE FROM STUDENT WHERE S_ID=1;--WHEN U DELETE A ROW IN MASTER TABLE OCCURE ERROR SQL Error: ORA-02292: integrity constraint (KUMAR.SS_ID_FK) violated - child record found

DROP TABLE STUDENT_ADDRESS;

SELECT * FROM STUDENT_ADDRESS;--NO RECORDS FOUND 

After drop a table,create one new table with foreign key with on delete cascde option
CREATE TABLE STUDENT_ADDRESS(ADDRESS VARCHAR2(10),
S_ID NUMBER,
CONSTRAINT SS_ADD_FK
FOREIGN KEY (S_ID) REFERENCES STUDENT(S_ID) ON DELETE CASCADE);

INSERT INTO STUDENT_ADDRESS VALUES('JMD',1);
INSERT INTO STUDENT_ADDRESS VALUES('BGLR',2);
INSERT INTO STUDENT_ADDRESS VALUES('DELHI',3);

SELECT * FROM STUDENT_ADDRESS;

DELETE FROM STUDENT WHERE S_ID=1;
---Now directly delete the record from employee table deletes the related child record from STUDENT_ADDRESS table automatically.

Comments or Responses

Login to post response