Q

Using the ON DELETE CASCADE in Oracle

I have following tables;

 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;

This was first published in October 2004

Dig deeper on Oracle database design and architecture

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close