Using the ON DELETE CASCADE in Oracle
I have following tables;
Continue Reading This Article
Enjoy this article as well as all of our content, including E-Guides, news, tips and more.
TABLE TEL_ACESFLAG_MST ( FLAG_NM CHAR(1) NOT NULL, FLAG_DESC VARCHAR2(28) NOT NULL ) TABLE TEL_ADMIN_MST ( SA_ID NUMBER(3) NOT NULL, SA_NM VARCHAR2(40) NOT NULL, SA_PASWORD VARCHAR2(50), SA_EMAILID VARCHAR2(40), SA_MOBNO NUMBER(13), SA_OFFPHNO NUMBER(13) ) TABLE TEL_COMPANY_MST ( CO_ID VARCHAR2(5) NOT NULL, CO_NM VARCHAR2(40) NOT NULL, CO_ADDR VARCHAR2(60) NOT NULL, CO_CT VARCHAR2(10) NOT NULL, CO_PHNO NUMBER(13) ) TABLE TEL_DEPT_MST ( DEPT_ID NUMBER(5) NOT NULL, DEPT_NM VARCHAR2(30) NOT NULL, CO_ID VARCHAR2(5) NOT NULL ) TABLE TEL_GRD_MST ( GRD_ID NUMBER(5) NOT NULL, GRD_NM VARCHAR2(20) NOT NULL, CO_ID VARCHAR2(5) ) TABLE TEL_LOC_MST ( LOC_ID NUMBER(5) NOT NULL, LOC_NM VARCHAR2(20) NOT NULL, CO_ID VARCHAR2(5) NOT NULL ) TABLE TEL_EMP_DTL ( EMP_ID VARCHAR2(20) NOT NULL, CO_ID VARCHAR2(5) NOT NULL, FRST_NM VARCHAR2(20) NOT NULL, LST_NM VARCHAR2(20) NOT NULL, LOC_ID NUMBER(5) NOT NULL, EMAIL_ID VARCHAR2(40), PASSWORD VARCHAR2(50), DEPT_ID NUMBER(5) NOT NULL, GRD_ID NUMBER(5) NOT NULL, OFF_PHNO NUMBER(13), MOB_NO NUMBER(13), RES_PHNO NUMBER(13), IS_ADMIN CHAR(1) DEFAULT 'N' NOT NULL, ACES_FLAG CHAR(1) NOT NULL )
Please suggest how I should implement the ON DELETE CASCADE feature in Oracle. Can you suggest how to relate the tables?
Only you can determine the relationship between your tables. Those relationships are defined by your business rules. So you'll have to find out your business rules to know the relation. Once you find out how your tables are related, you are ready to implement Foreign Key constraints for your tables.
A Foreign Key ties a column (or columns) in one table to a column (or columns) in a parent table. First, you'll need to make sure that the parent table has a primary key. If not, you can add the primary key as follows:
ALTER TABLE parent_table ADD CONSTRAINT parent_key_pk PRIMARY KEY (colX);
Now that the parent table has a primary key constraint, you can have a column of the child table reference the parent as follows:
ALTER TABLE child_table ADD CONSTRAINT child_parent_fk REFERENCES parent_table(colX);
If you want to add the ON DELETE CASCADE option, then you can use this statement:
ALTER TABLE child_table ADD CONSTRAINT child_parent_fk REFERENCES parent_table(colX) ON DELETE CASCADE;