Oracle Truncate Parent table constraint error

Question

Parent table A with some records. Child table B with 0 records.

I have a cursor to do truncation on these tables in reverse tree order (i.e. truncate child first, then parent) But I'm still getting this error when I truncate the parent table 'ORA-02266: unique/primary keys in table referenced by enabled foreign keys' error, despite the fact that the child table has no records at all.

However, when I do 'Delete from ' in the same order, all constraint related errors were avoided. And 'Delete from' takes really long time to run.

I don't want to disable or drop any constraints.

Answer 1

However, when I do 'Delete from ' in the same order, all constraint related errors were avoided.

That's the only way if you have constraints enabled. You cannot truncate. If you still want to truncate the table, then you could find the constraint name from user_constraints, and then DISABLE them:

ALTER TABLE table_name DISABLE CONSTRAINT constraint_name;

Then you could TRUNCATE the table, and re-enable the constraint:

ALTER TABLE table_name ENABLE CONSTRAINT constraint_name;

If your table has ON DELETE CASCADE option, then from Oracle 12.1 onward, you could use:

TRUNCATE TABLE table_name CASCADE;

Note, both the DELETE CASCADE and the TRUNCATE CASCADE will fail if any of the relationships in the hierarchy are not defined with the ON DELETE CASCADE clause.

Answer 2

There are three ways to delete from parent/child tables:

CREATE TABLE par (i NUMBER CONSTRAINT par_pk PRIMARY KEY);
CREATE TABLE chi (i NUMBER CONSTRAINT chi_fk REFERENCES par(i) ON DELETE CASCADE);
INSERT INTO par VALUES(1);
INSERT INTO chi VALUES(1);

1) If you have Oracle 12 or later, and you have foreign keys with ON DELETE CASCADE, you can use TRUNCATE CASCADE, which I expect to be the fastest option:

TRUNCATE TABLE par CASCADE;
Table PAR truncated.

SELECT count(*) FROM par;
0

SELECT count(*) FROM chi;
0

2) If your foreign keys are defined with ON DELETE CASCADE, you can use DELETE, which I expect to be the slowest option:

DELETE FROM par;
1 row deleted.

SELECT count(*) FROM par;
0

SELECT count(*) FROM chi;
0

3) Otherwise, you can disable the foreign keys, truncate the tables and reenable the foreign keys. This is fast, but is a little bit more risky than the other options (please check that the constraints are all valid afterwards):

ALTER TABLE chi DISABLE CONSTRAINT chi_fk;
TRUNCATE TABLE chi;
TRUNCATE TABLE par;
ALTER TABLE chi ENABLE  CONSTRAINT chi_fk;

AFAIK, it is not possible to alter a foreign key from normal to ON DELETE CASCADE. I guess you have to drop and recreate them:

ALTER TABLE chi DROP CONSTRAINT chi_fk;
ALTER TABLE chi ADD  CONSTRAINT chi_fk REFERENCES par(i) ON DELETE CASCADE;