Skip to Main Content

Oracle Database Discussions

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Drop partition without disabling foreign key

772262Jul 31 2012 — edited Jul 31 2012
Hi All,

I have parent and child table.

Parent table

create table parent_1
(id number,
create_date date,
constraint parent_1_pk001 PRIMARY KEY (id))
PARTITION BY RANGE (create_date)
INTERVAL (NUMTODSINTERVAL(1,'DAY'))
(PARTITION parent_1_part VALUES LESS THAN ('01-JAN-2010'));

Child Table
create table child_1
(id number,
create_date date,
constraint child_1_fk001 FOREIGN KEY (id)
REFERENCES parent_1 (id))
PARTITION BY RANGE (create_date)
INTERVAL (NUMTODSINTERVAL(1,'DAY'))
(PARTITION create_date_part VALUES LESS THAN ('01-JAN-2010'));


I am having problems dropping partition.
Parent_1
1 26-JUL-12
2 26-JUL-12

Child_1
1 26-JUL-12

alter table CHILD_1 drop partition SYS_P274;
table CHILD_1 altered.

ON DROPPING PARENT PARTITION
alter table parent_1 drop partition SYS_P273;

Error report:
SQL Error: ORA-02266: unique/primary keys in table referenced by enabled foreign keys
02266. 00000 - "unique/primary keys in table referenced by enabled foreign keys"
*Cause: An attempt was made to truncate a table with unique or
primary keys referenced by foreign keys enabled in another table.
Other operations not allowed are dropping/truncating a partition of a
partitioned table or an ALTER TABLE EXCHANGE PARTITION.
*Action: Before performing the above operations the table, disable the
foreign key constraints in other tables. You can see what
constraints are referencing a table by issuing the following
command:
SELECT * FROM USER_CONSTRAINTS WHERE TABLE_NAME = "tabnam";


PLEASE CAN I KNOW IF THERE IS ANY WAY TO DROP PARENT PARTITION WITHOUT DISABLE/ENABLE FOREIGN CONSTRAINTS


Thanks
This post has been answered by Mohamed Houri on Jul 31 2012
Jump to Answer
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Aug 28 2012
Added on Jul 31 2012
3 comments
4,534 views