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

• Original Message ----- From: "Jacques Kilchoer" <Jacques.Kilchoer_at_quest.com> To: <granaman_at_cox.net>; <oracle-l_at_freelists.org> 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 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.

--
http://www.freelists.org/webpage/oracle-l