0

I have two tables, a parent and child table, which have referential integrity declared between two tables and try to move both the tables so I can delete rows.

I start with the child table and the move works fine and the rows are deleted. I then attempt a move on the parent table and the move fails.

I can drop the Foriegn Key and complete the move on the parent table leaving me with the same number of rows in both the parent and child table, which is what I expected.

What I don't understand is why the move on the parent table fails as there are no child references.

What I find interesting is if I set up ON CASCADE DELETE the delete on both the parent and child work fine with just a delete on the parent table.

Can some please explain to me why the move on the parent fails even though there are no child rows. I would have expected this to work.

I am running on Oracle 19.2 and also tested this using LIVESQL. Below is my detailed test case.


create table parent
as
select  *
from    all_objects
where   rownum <= 10000;
 
alter table parent add constraint par_pk primary key(object_id);
 
create table child
as
select  *
from    parent
order by      dbms_random.value;
 
alter table child add constraint chi_pk primary key(object_id);
alter table child add constraint chi_fk_par foreign key(object_id) references parent;

I’ve created the child table from the parent data, with random ordering. Now I’m going to delete all the child rows where owner = ‘PUBLIC’ using an online move, then I’ll try and do the same for the parent.

SELECT count(*) from child 

COUNT(*)
10000

alter table child move
        including rows where owner != 'PUBLIC'
        online
;
 

Child move succeeds

SELECT count(*) from child 

COUNT(*)
4014

alter table parent move
        including rows where owner != 'PUBLIC'
        online
;
 

Trying to do the matching move on the parent results in:

ORA-02266: unique/primary keys in table referenced by enabled foreign keys

Now when I remove the foriegn key and do the move everything appears to work.

alter table child disable CONSTRAINT chi_fk_par;

alter table parent move
        including rows where owner != 'PUBLIC'
        online
;

SELECT count(*) from parent 

COUNT(*)
4014

alter table child enable CONSTRAINT chi_fk_par;

2 Answers 2

0

You are using a filter condition on a table whose PK is referenced by an FK.

From Oracle Documentation

Filter conditions are unsupported for tables with primary or unique keys that are referenced by enabled foreign keys.

0

It fails because there is a foreign key, not because of integrity: the move is DDL, not DML.

1
  • @Michael Kutz thanks I get it now
    – Pugzly
    Commented Dec 25, 2021 at 15:20

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.