Skip to Main Content
  • Questions
  • Add ON DELETE CASCADE to foreign key used in partition by reference

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Z.

Asked: May 24, 2017 - 2:31 pm UTC

Last updated: February 12, 2019 - 10:50 am UTC

Version: 11g

Viewed 10K+ times! This question is

You Asked

Is it possible to add ON DELETE CASCADE to a foreign key that is used as a partitioning key when used with PARTITION BY REFERENCE?

My solution would be to drop the constraint and recreate it, but it doesn't work, as I cannot drop a constraint that is used by PARTITION BY REFERENCE.

I get SQL Error: ORA-14650: operation not supported for reference-partitioned tables

Oracle 11g.

drop table y;
drop table x;
create table x (a number primary key) partition by hash (a);
create table y (a number not null,
   constraint y_x_fk foreign key(a) references x(a))
   partition by reference(y_x_fk);
alter table y drop constraint y_x_fk;

and Chris said...

Yes:

create table x (a number primary key) partition by hash (a);

create table y (a number not null,
  constraint y_x_fk foreign key(a) references x(a) on delete cascade
) partition by reference(y_x_fk);

insert into x values (1);
insert into y values (1);
select count(*) from y;

COUNT(*)  
1 

delete x where a = 1;

select count(*) from y;

COUNT(*)  
0  

Rating

  (2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Z M, May 24, 2017 - 3:18 pm UTC

Thanks for the answer. I might have not written it completely clearly, but I want to add ON DELETE CASCADE to an existing table. I am not creating a new table, I am trying to modify an existing one. Hence my proposed solution of recreating the original constraint, rather than changing the original constraint in CREATE TABLE statement.
Connor McDonald
May 25, 2017 - 1:30 am UTC

Yes, that is a restriction of reference partitioning. Moreover, there is almost nothing you can do with the child table in terms of temporarily or permanently disassociating it with the parent without actually dropping the child.

So you'd be heading for a maintenance activity here.


A reader, February 12, 2019 - 3:28 am UTC

Example here https://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:9534887800346858804
shows child partition created without "on delete cascade" and paren(and child) partitions have been successfully dropped. In your example you tell us its mandatory to specify "on delete cascade" in order to drop partitions. It is not clear where is the difference.
Chris Saxon
February 12, 2019 - 10:50 am UTC

That refers to dropping the whole parent partition. Using an on delete cascade FK allows you to delete a single row from the parent partition. And remove the child row automagically.

More to Explore

VLDB

If you are new to partitioning, check out Connor McDonald's introduction series here.

VLDB

Documentation set on VLDB and Partitioning.