RE: drop partition - with foreign keys enabled?

  • From: "Jacques Kilchoer" <Jacques.Kilchoer@xxxxxxxxx>
  • To: "Don Granaman" <granaman@xxxxxxx>, <oracle-l@xxxxxxxxxxxxx>
  • Date: Thu, 20 Jan 2005 10:32:40 -0800

Yes, the documentation is wrong. Where did it say this in the
documentation? I couldn't find it.
I can drop a partition from order_detail without disabling
-----Original Message-----
From: Don Granaman [mailto:granaman@xxxxxxx]
Sent: mercredi, 19. janvier 2005 23:10
To: Jacques Kilchoer; oracle-l@xxxxxxxxxxxxx
Subject: Re: drop partition - with foreign keys enabled?

[I replied to this last night, but it was rejected for over-quoting.]

Perhaps I was not clear...

The situation is, using your example, that the docs say that to drop (or
exchange) a partition from the (referencing) ORDER_DETAIL table, one has
first disable the foreign key ORDER_DETAIL_FK1.  This is what seems odd.
One would not need to disable this foreign key prior to truncating a
non-partitioned ORDER_DETAIL table.  I suspect that this is simply a
documentation bug.

It does makes perfect sense that one would have to disabled the
ORDER_DETAIL_FK1 constraint prior to dropping (or exchanging) a
from the (referenced) ORDERS table - for the reason you mentioned.

Don Granaman

----- Original Message -----
From: "Jacques Kilchoer" <Jacques.Kilchoer@xxxxxxxxx>
To: <granaman@xxxxxxx>; <oracle-l@xxxxxxxxxxxxx>
Sent: Tuesday, January 18, 2005 11:17 AM
Subject: RE: drop partition - with foreign keys enabled?

> I don't understand your example too well. Let me rephrase it perhaps.
> Let me take the tables ORDERS and ORDER_DETAIL with the following
> constraints:
> ORDERS : primary key ORDERS_PK (order_id)
> ORDER_DETAIL : primary key ORDER_DETAIL_PK (order_id, item_id)
>                foreign key ORDER_DETAIL_FK1 (order_id) references
> (order_id)
> If ORDERS is a partitioned table, I cannot drop a partition from
> because of the FK constraint ORDER_DETAIL_FK1 (ORA-02266). If I were
> able to do the drop partition, Oracle would have to read all of the
> ORDER_DETAIL table to make sure that I am not deleting values that are
> present in the child table, and the Oracle programmers didn't bother
> coding that functionality. To drop a partition (or exchange partition)
> from a parent table you will need to disable the foreign key
> If ORDER_DETAIL is a partitioned table, I can drop a partition from
> ORDER_DETAIL. Any global indexes on ORDER_DETAIL will become invalid
> need to be rebuilt. For more efficiency, I would avoid building global
> indexes.
> This is the way it has always worked.
> I'm not sure what you think is odd.


Other related posts: