Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> Re: drop partition - with foreign keys enabled?

Re: drop partition - with foreign keys enabled?

From: Don Granaman <>
Date: Wed, 19 Jan 2005 23:10:11 -0800
Message-ID: <001c01c4febf$1524abf0$6401a8c0@dilbert>

[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 to 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 partition from the (referenced) ORDERS table - for the reason you mentioned.

Don Granaman

> 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 ORDERS
> (order_id)
> If ORDERS is a partitioned table, I cannot drop a partition from ORDERS
> 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 constraint.
> If ORDER_DETAIL is a partitioned table, I can drop a partition from
> ORDER_DETAIL. Any global indexes on ORDER_DETAIL will become invalid and
> 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.

Received on Thu Jan 20 2005 - 00:15:41 CST

Original text of this message