RE: drop partition - with foreign keys enabled?

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 order_detail_fk1.
-----Original Message-----
From: Don Granaman [mailto:granaman_at_cox.net] Sent: mercredi, 19. janvier 2005 23:10
To: Jacques Kilchoer; oracle-l_at_freelists.org 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 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