Skip to Main Content

SQL & PL/SQL

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!

Truncate partitions: ORA-02266: unique/primary keys in table referenced by enabled foreign keys

vpolasa10 hours ago — edited 7 hours ago

I'm trying to truncate few partitions on below table but seeing ORA-02266 though there is no dependency on other partitions. I understand this can be addressed by disabling the foreign key constraint. Is there another approach to truncate few partitions without disabling the constraint?

From below example, data in partitions region_east, region_west, region_south is completely independent of region_central. Though I'm trying to truncate partitions: region_east, region_west, region_south, I'm seeing ORA-02266.

CREATE TABLE test_region (
    item_id NUMBER,
    item_quantity NUMBER,
    state_code VARCHAR2(2), 
    item_id_prior NUMBER,
    CONSTRAINT region_pk PRIMARY KEY (item_id),
    CONSTRAINT item_id_prior_fk FOREIGN KEY (item_id_prior) REFERENCES test_region(item_id)
)
PARTITION BY LIST (state_code) (
    PARTITION region_east VALUES ('MA', 'NY'),
    PARTITION region_west VALUES ('CA', 'AZ'),
    PARTITION region_south VALUES ('TX', 'KY'),
    PARTITION region_central VALUES ('OH', 'ND')
);

INSERT INTO test_region (item_id, item_quantity, state_code, item_id_prior) VALUES (1, 10, 'MA', null);
INSERT INTO test_region (item_id, item_quantity, state_code, item_id_prior) VALUES (2, 20, 'CA', 1);
INSERT INTO test_region (item_id, item_quantity, state_code, item_id_prior) VALUES (3, 15, 'AZ', 1);
INSERT INTO test_region (item_id, item_quantity, state_code, item_id_prior) VALUES (4, 12, 'NY', 3);
INSERT INTO test_region (item_id, item_quantity, state_code, item_id_prior) VALUES (5, 3, 'TX', 2);
INSERT INTO test_region (item_id, item_quantity, state_code, item_id_prior) VALUES (6, 22, 'OH', NULL);
INSERT INTO test_region (item_id, item_quantity, state_code, item_id_prior) VALUES (7, 2, 'ND', 6);
ALTER TABLE test_region TRUNCATE PARTITION region_east, region_west, region_south

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";

Working on:

Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Windows 11 Enterprise

Comments
Post Details
Added 10 hours ago
2 comments
39 views