Oracle parent child move failed



  • 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 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;



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




Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2