Truncate parent table in Oracle when child table is empty

oraclereferential-integritytruncate

Suppose I have a parent table parent referenced by a child table child. The table parent is populated but child is not. Attempting to truncate parent results in

 ORA-02449: unique/primary keys in table referenced by foreign keys

Is there a way to hint to the DBMS that child is empty, so that the foreign key constraint doesn't need to be disabled?

Best Answer

No, there is no such hint. What you can do is

alter table {child_table} disable constraint {fk_constraint_name};
truncate table {parent_table};
alter table {child_table} enable constraint {fk_constraint_name};

Related Solutions

Using Multi-table Insert for Parent and Child Table

No, you can't depend on this. SQL is declarative, not procedural, so within a statement you can't guarantee the order of execution. Since the entire INSERT ALL statement is considered a single statement (doc), you can't guarantee that one INSERT will be before another.

By definition an INSERT FIRST must execute the first INTO passing the evaluated conditions. We might expect INSERT ALL to behave similarly. This appears to be the case:

DROP TABLE T1;
CREATE TABLE T1 AS (SELECT 'a' c1, 0 c2, 0 c3 FROM dual WHERE 1=2);
INSERT ALL 
   WHEN mod(x,2)<>0 THEN INTO T1 VALUES ('a', x, mod(x,2)) 
   WHEN mod(x,2)=0 THEN INTO T1 VALUES ('b', x, mod(x,2)) 
   SELECT Level x FROM dual CONNECT BY Level <=20;
COMMIT;   
SELECT rowid, c1, c2, c3 FROM t1;

However, even though we can demonstrate a particular behavior on a particular platform/version/patchset still doesn't make this a guarantee.

Oracle-developer.net says it explicitly:

the conditions in an INSERT FIRST statement will be evaluated in order from top to bottom. Oracle makes no such guarantees with an INSERT ALL statement.

Sql-server – How to change Referential Integrity from a Unique Key Constraint to a Primary Key

There's currently no way to modify which unique index a foreign key constraint is "attached to" because this is supposed to be an internal implementation detail, the same way that a unique constraint uses a unique index behind the scenes to enforce the constraint.

In fact, as you saw, if you have multiple candidate indexes, the one the foreign key attaches to seems non-deterministic (it might go by index id, index width, or something else; I haven't played with it in-depth), and it cannot be specified in the DDL statement either (memory says there's a Connect item requesting that feature, though).

So in this case, at least for now, you'll need to drop both the foreign key and the existing unique constraint before re-establishing the relationship, to ensure your change script works correctly in all scenarios.

Best Answer

Related Solutions

Using Multi-table Insert for Parent and Child Table

Sql-server – How to change Referential Integrity from a Unique Key Constraint to a Primary Key

Related Question