CODEWITHSUNDEEP
oracle-database
Rajesh Kumar
Rajesh Kumar

Reputation: 59

Constraints error during partitions Drop

When i am trying to drop partitions in oracle i am facing the below mentioned error.

Error report: SQL Error: ORA-02266 - "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";

i have few system constraints and few developed by me so do i need to disable the system constraints also to drop partitions, attached is the screenshot of constraints.enter image description here

Upvotes: 0

Views: 3057

Answers (1)

Aleksej
Aleksej

Reputation: 22949

I believe an example will clarify. Say I have a master table, partitioned, and a detail table that references master with a FK:

SQL> create table master(col1 number, part number)
  2  partition by list(part) (
  3      partition p0 values (0),
  4      partition p1 values (1),
  5      partition p2 values (2)
  6  );

Table created.

SQL> alter table master add constraint master_pk primary key ( col1, part);

Table altered.

SQL> create table detail(col1 number, part number, col2 varchar2(100));

Table created.

SQL> alter table detail add constraint master_detail_fk foreign key ( col1, part) references master ( col1, part);

Table altered.

I insert some rows in both tables, according to the FK constraint:

SQL> insert into master (col1, part) select level, mod(level, 3) from dual connect by level < 10;

9 rows created.

SQL> insert into detail (col1, part, col2) select level, mod(level, 3), 'something' from dual connect by level < 10;

9 rows created.

Now I try to drop a partition in the master:

SQL> alter table master drop partition p1;
alter table master drop partition p1
*
ERROR at line 1:
ORA-02266: unique/primary keys in table referenced by enabled foreign keys

I need to handle the FK before dropping:

SQL> alter table detail disable constraint master_detail_fk ;

Table altered.

SQL> alter table master drop partition p1;

Table altered.

Of course, if I dropped referenced rows, the constraint will not be validated:

SQL> alter table detail enable validate constraint master_detail_fk ;
alter table detail enable validate constraint master_detail_fk
                                              *
ERROR at line 1:
ORA-02298: cannot validate (SIUCREDITO.MASTER_DETAIL_FK) - parent keys not
found

SQL> alter table detail enable novalidate constraint master_detail_fk ;

Table altered.

SQL>

Upvotes: 1

Related Questions