ORA-02266: Unique/Primary Keys In Table Referenced While Truncating Table

 

Trying to do a data only import into a table and hitting the error below :

ORA-39120: Table "XYZ"."LOOKUPS" can't be truncated, data will be skipped. Failing error is:

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

The primary key in the table( which we are truncating) , seems to be referring to a another child table with foreign key.

Run below script to get the parent table , child table relation

set lines 200

COL CHILD_TABLE FOR a50

col CONSTRAINT_NAME for a50

col owner form a40

col FK_column form a40

col table_name form a50

select b.owner, b.table_name child_table,  c.column_name FK_column, b.constraint_name

    from dba_constraints a, dba_constraints b, dba_cons_columns c

    where a.owner=b.r_owner

    and b.owner=c.owner

    and b.table_name=c.table_name

    and b.constraint_name=c.constraint_name

    and a.constraint_name=b.r_constraint_name

    and b.constraint_type='R'

  and a.owner='&owner'

   and a.table_name='&table_name'

  and a.CONSTRAINT_TYPE='P'

Enter the values and then do a manual - alter table disable constraint.

For tables with multiple constraints this is easier i.e.

  

 select 'alter table ' || owner || '.' || table_name || ' disable constraint ' || constraint_name ||';' stmt from all_constraints

  where r_constraint_name in (select constraint_name from all_constraints where table_name = 'LOOKUPS')

Run the output - remember to enable the constraints when complete - the db I was doing this was restricted so no issue of any others "hitting" the tables.

Import should now work.