Thanks Mr. Tom for the quick response.

Your answer was very useful, but what about if the user wants to know the name of the dependant tables?

I appreciate your help.

Best regards.

Tom,

I know Oracle implements ON CASCADE DELETES up to release 8i.
Other forms have to be implemented through code (i.e. trigers).
Do you know if in the next versions (9i,...) there will be other cascade options like:

- ON UPDATE CASCADES;
- ON DELETE NULLIFIES;
- etc...

that already exist in other rdms's a long time ?

Thanks again !

Well, with regards to ON UPDATE CASCADE, I hope we never implement it.  You should never need it (if you do, you've done your design utterly wrong).  

See

for a way to do it in 7.x and up however (in 90% of the cases).  Should give you an appreciation of how nasty this is.

Additionally, you could create your constraints DEFERRABLE, set them to be deferred and easily accomplish the update cascade via a procedure (but again, if you are finding a constant, recurring need for this -- YOU'VE DONE YOUR JOB WRONG).

For the ON DELETE SET NULL

ops$tkyte@ORA815.US.ORACLE.COM> create table p ( x int primary key );

Table created.

ops$tkyte@ORA815.US.ORACLE.COM> create table c ( x references p on delete set null );

Table created.

works in Oracle8i release 1 (815) and up.  

As for the "etc" -- I'm not aware of any "etc's" to be handled beyond these two.
 

I was now aware of the ON DELETE SET NULL feature, starting in 8.1.5.

Tom,

Sorry, but I did not quite get why this is so bad.

Just a note... If I am not mistaken, DB2 has this feature, which set the standard for SQL. By the way I studied DBMSs using Date's book based on DB2's SQL.

I imagine a very simple case: I want to update the DEPTNO primary key on DEPT table, and I want all chldren in EMP to have the DEPTNO foreign key updated.

What could be so bad ?

Locking ? Let us set up the right indexes.

Hi Tom,

As usual learning alot from your site.

I never new that you can do "SET NULL" in ON DELETE CASCADE constraint.

Which Data Dictionary will tell me whether it was

ON DELETE CASCADE or
ON DELETE SET NULL

I looked into USER_CONSTRAINTS and DBA_CONSTRAINTS.

It did not show any information there. The Column DELETE_RULE in both shows "NO ACTION".

Is there any method or only method is insert a row in parent & child and delete it and see what happens?

Thanx in Advance.

Regards,
Sagi

Hi Tom
After reading R92 document I found
--
ON DELETE Clause
The ON DELETE clause lets you determine how Oracle automatically maintains referential integrity if you remove a referenced primary or unique key value. If you omit this clause, then Oracle does not allow you to delete referenced key values in the parent table that have dependent rows in the child table.

Specify CASCADE if you want Oracle to remove dependent foreign key values.
Specify SET NULL if you want Oracle to convert dependent foreign key values to NULL.
--
Here it says dependent foreign key values. And surprisingly I found table created by some one had this:

alter table MODEL_RUNS
add constraint MDR_MDR_FK foreign key (PARENT_MULTI_MDR_ID)
references MODEL_RUNS (MDR_ID) on delete cascade;

To know the effect of this I altered EMP table like this

ALTER TABLE Emp ADD CONSTRAINT fk2_deptno
FOREIGN KEY (deptno)
REFERENCES scott.Dept (deptno) ON DELETE CASCADE;

and then:

scott@ORA9I> delete from emp where deptno=20;

5 rows deleted.

Elapsed: 00:00:00.00
scott@ORA9I> select * from dept where deptno=20;

DEPTNO DNAME LOC
---------- -------------- -------------
20 RESEARCH DALLAS

Elapsed: 00:00:00.00
scott@ORA9I>

only EMP records were deleted.
Could you please explain what's the purpose and why should it be done and what could be the effect.
This also never gave any error while adding constraint or deleting.

Thnaks.

Thanks Tom and you made it simple - cascade down

It appears that the only way to remove the ON DELETE clause is to drop and re-create the constraints. This seems like something that one should be able to ALTER -- am I just missing it?

Oracle v9.2.0.6

Hi Tom,

we have some performance issue with cascade deletes.

we have a bunch of tables, around 40, which are children of 3 main tables. many of the tables has more than 10 million records. i have to delete around 100K old records. we have indexes on all foreign keys.

our DBAs suggested not to use cascade to delete data. They were telling that context has to switch for every child to delete data from it.

we implemented that and it turned out to be a disaster as main tables are taking 5hrs each to delete even though we deleted the children's data before them.

we do you suggest?

go by cascade?

is there anyway that i can hint saying that don't check for children while deleting?

or is there any better way to solve the problem?

Tom,

Could you please help me with this error. What did I do wrong because I followed syntax diagram.

Thank you for your time

SQL> alter table emp modify constraint fk_deptno references dept (deptno) on delete cascade;
alter table EMP modify constraint fk_deptno references DEPT (deptno) on delete cascade
                                            *
ERROR at line 1:
ORA-00933: SQL command not properly ended 

Tom,

I forgot to mention in my above post that I was trying to modify constraint adding 'ON DELETE CASCADE'

Thank you.

Hi tom
I know you dont accept new questions but please answer this one, its really really important,answer it just this time only.

I have child and parent tables , now i havent defined ON CASCADE DELETE clause in create table defination, now if i want to delete the rows from both parent and child tables i cant.. coz i dont have ON CASCADE DELETE clause .

Now i know the way you said before also that by droping the constaint and add it again using alter table....on cascade delete.

The reason i dont want to use this mehtod is because i have lots of tables and it would be a long process besides if someone accidently deletes rows from parent table then rows from child table would alos be deleted.

I hope you understand my problem, please suggest any other approach for this problem.

Thanks for the response

i created one parent table and one child table.
now i want to delete only the parent record.i must have the child records.is there any possible way