Re: drop partition - with foreign keys enabled?
- From: "Don Granaman" <granaman@xxxxxxx>
- To: <Jacques.Kilchoer@xxxxxxxxx>, <oracle-l@xxxxxxxxxxxxx>
- Date: Wed, 19 Jan 2005 23:10:11 -0800
[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@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 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.
--
//www.freelists.org/webpage/oracle-l
Other related posts:
