Foreign Key (ON DELETE CASCADE)

Look at this:

create TABLE BGW.YYY (
    ID number not null,
    PARENT_ID number not null references XXX(ID),
    PASSWORD varchar2(254) null
) TABLESPACE SSS;

Is it the same as the following two DDL?

create TABLE BGW.YYY (
    ID number not null,
    PARENT_ID number not null,
    PASSWORD varchar2(254) null
) TABLESPACE SSS;

ALTER TABLE BGW.YYY ADD (
    CONSTRAINT FK_YYY FOREIGN KEY (PARENT_ID)
    REFERENCES BGW.XXX (ID)
);

Do I need to add ON DELETE CASCADE to the ALTER TABLE statement to make it equivalent to the first CREATE TABLE statement?
LVL 9
yongsingAsked:
Who is Participating?
 
plamen73Commented:
yes, they are equivalent;
In order to make it on delete cascade you have to:
1. option 1 - one statement:
create TABLE BGW.YYY (
   ID number not null,
   PARENT_ID number not null references XXX(ID) ON DELETE CASCADE,
   PASSWORD varchar2(254) null
) TABLESPACE SSS;

2. option 2 - two statements:
create TABLE BGW.YYY (
   ID number not null,
   PARENT_ID number not null,
   PASSWORD varchar2(254) null
) TABLESPACE SSS;

ALTER TABLE BGW.YYY ADD (
   CONSTRAINT FK_YYY FOREIGN KEY (PARENT_ID)
    REFERENCES BGW.XXX (ID) ON DELETE CASCADE
);
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.