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