Drop partition without disabling foreign key
772262Jul 31 2012 — edited Jul 31 2012Hi 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