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

This error is easily explained with the help of a picture, courtesy of Oracle Application Express Model View:


Suppose you have a LOCATIONS table with a column LOCATION_ID defined as primary key (or with a unique constraint on it), then another table called STORES, with a column called LOCATION_ID and a foreign key constraint called FK_LOCATION_ID that is referencing the LOCATION_ID column of table LOCATIONS (graphically rendered in the picture by the shape -[ which represents a one-to-many relationship).

No matter if this constraint is defined as ON DELETE CASCADE or ON DELETE SET NULL or the default (NO ACTION), if you try to do the following:

TRUNCATE TABLE LOCATIONS
/

You will get:

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

Please note that the error is raised regardless of the presence of data in the child tables.

It is perfectly normal that Oracle prevents you from doing this, however if all child tables are empty, then you can safely disable the referential constraints and re-issue the TRUNCATE.
But don't forget to re-enable the constraints afterwards.

If you need to purge a whole set of these intertwined tables, such as when you need to reset a test scenario before reloading a test data set, then it can be useful to have a script that temporarily disables all the constraints on the child tables and iteratively truncates all of them.

Of course you need to make sure that you (or someone else...) do not run under any circumstances such script in a production environment.

Note that you can use SQL queries to spawn the DDL statements required to perform this tasks and spool the results as a SQL script that you call right away.
First i create a list of currently enabled constraints, because i don't want to enable constraints that were disabled for some other reason, then i create the disabling statements and finally the truncate statements. Once all the auto-generated scripts are ready, i can run them in the sequence: disable-truncate-enable.

create procedure force_truncate(
  csv_of_tables            IN VARCHAR2,
  display_only             IN BOOLEAN DEFAULT TRUE
)
is
--
  list_of_tables           DBMS_UTILITY.UNCL_ARRAY;

  TYPE str_tab             IS TABLE OF VARCHAR2(255);
  tab_of_DDLs              str_tab;

  TYPE assoc_tab           IS TABLE OF VARCHAR2(5) INDEX BY VARCHAR2(61);
  trunc_list               assoc_tab;

  tmp                      INTEGER;
  n                        INTEGER;
  stop_on_err              BOOLEAN    DEFAULT FALSE;
  show_warning             BOOLEAN    DEFAULT FALSE;

  cursor cur_child_tables(p_tab_name in user_tables.table_name%type) is
         select distinct owner||'.'||table_name
                as child_name
          from user_constraints
          where (r_constraint_name, r_owner)
             in (select constraint_name, owner
                   from user_constraints
                  where constraint_type in ('P','U')
                    and table_name = p_tab_name
                    and status = 'ENABLED');

  cursor cur_tables(p_tab_name in user_tables.table_name%type) is
          select 1 as ok
            from user_tables
           where table_name = p_tab_name;

  cursor cur_grantees(p_tab_name in user_tab_privs_made.table_name%type) is
          select grantee
            from user_tab_privs_made
           where privilege = 'REFERENCES'
             and table_name = p_tab_name;

  cursor cur_constraints(p_tab_name in user_tables.table_name%type) is
         select owner, table_name, constraint_name
          from user_constraints
          where (r_constraint_name, r_owner)
             in (select constraint_name, owner
                   from user_constraints
                  where constraint_type in ('P','U')
                    and table_name = p_tab_name
                    and status = 'ENABLED');

-- private subroutine                   
 procedure exec_and_log(
   DDL               IN VARCHAR2,
   display_only      IN BOOLEAN
 )
 is
 begin
   dbms_output.put_line(DDL);
   dbms_output.put_line('/');
   if not display_only then
     begin
       execute immediate DDL;
       dbms_output.put_line('--done');
     exception
     when others then
       dbms_output.put_line(sqlerrm);
     end;
   end if;
 end;
-- main procedure
begin
 if display_only then
    dbms_output.put_line('running in display-only mode, DDLs will not be executed!');
    dbms_output.new_line;
 else
    dbms_output.put_line('running DDLs, check for execution errors');
    dbms_output.new_line;
 end if;

 dbms_output.new_line;

 dbms_utility.comma_to_table(upper(csv_of_tables), n, list_of_tables);
 tab_of_DDLs := str_tab();

 for each_entry in 1..n
 loop
   trunc_list(user||'.'||trim(list_of_tables(each_entry))) := '...ok';
 end loop;

 -- check if child tables may cause some trouble
 for each_entry in 1..n
 loop
   dbms_output.put_line('Checking...'||trim(list_of_tables(each_entry)));


   begin
     select 1
       into tmp
       from user_tables
      where table_name = trim(list_of_tables(each_entry));
   exception
   when no_data_found then
     stop_on_err := TRUE;
     dbms_output.put_line(trim(list_of_tables(each_entry))
                          ||' is not a user table');
   end;

   for each_grantee in cur_grantees(trim(list_of_tables(each_entry)))
   loop
      dbms_output.put_line('WARNING: user ' || each_grantee.grantee
          || ' has been granted REFERENCES on this table');
      show_warning := TRUE;
   end loop;

   if show_warning then
     dbms_output.new_line;
     dbms_output.put_line('Truncates on table '
                          ||trim(list_of_tables(each_entry))|| ' may fail');
     dbms_output.new_line;
     show_warning := FALSE;
   end if;

   for each_child in cur_child_tables(trim(list_of_tables(each_entry)))
   loop
     begin
       dbms_output.put(trim(list_of_tables(each_entry))
                       ||' has child table '||each_child.child_name);
       -- if next line fails, a not listed child table has been found
       dbms_output.put_line(trunc_list(each_child.child_name));
     exception
     when no_data_found then
       stop_on_err := TRUE;
       dbms_output.put_line('...not found in truncate list');
     when others then raise;
     end;
   end loop;
 end loop;

 if stop_on_err then
    dbms_output.put_line('aborting truncates...');
    return;
 end if;
 dbms_output.new_line;

 for each_entry in 1..n loop
                       -- prepare constraint enable for later use
   for each_constraint in cur_constraints(trim(list_of_tables(each_entry)))
   loop
       tab_of_DDLs.EXTEND;
       tab_of_DDLs(tab_of_DDLs.LAST) :=
          'ALTER TABLE '
          ||each_constraint.owner||'.'||each_constraint.table_name
          ||' ENABLE CONSTRAINT '||each_constraint.constraint_name;
                       -- perform disable of constraints
       exec_and_log('ALTER TABLE '
                    ||each_constraint.owner||'.'||each_constraint.table_name
                    ||' DISABLE CONSTRAINT '||each_constraint.constraint_name,
                    display_only);
   end loop;
                       -- perform truncates
   exec_and_log('TRUNCATE TABLE '||trim(list_of_tables(each_entry))
                ||' DROP STORAGE', display_only);

 end loop;
                       -- re-enable constraints
 if tab_of_DDLs.exists(1) then
   for each_ddl in tab_of_DDLs.FIRST..tab_of_DDLs.LAST loop
      exec_and_log(tab_of_DDLs(each_ddl), display_only);
   end loop;
 end if;
end;

Example:

force_truncate('LOCATIONS,STORES');

dbms_output:

Checking...LOCATIONS
WARNING: user YOCOYATX has been granted REFERENCES on this table
WARNING: user YOCOYA has been granted REFERENCES on this table

Truncates on table LOCATIONS may fail

LOCATIONS has child table TEST.STORES...ok
Checking...STORES

ALTER TABLE TEST.STORES DISABLE CONSTRAINT FK_LOCATION
/
TRUNCATE TABLE LOCATIONS DROP STORAGE
/
TRUNCATE TABLE STORES DROP STORAGE
/
ALTER TABLE TEST.STORES ENABLE CONSTRAINT FK_LOCATION
/

REMARKS:

1. The script above is provided with no warranty whatsoever, you can use it at your own risk.
2. By default the script works in display only mode. In order to perform the DDLs, pass FALSE to parameter display_only.
3. It is recommendable to run the procedure in display_only mode first and then to inspect the output.
4. You have the option of copying and pasting the output statements in a sql script and run them manually if you prefer.
   
5. The script checks if the supplied list of tables includes all child tables referenced by foreign keys and it quits if the condition is not met. This security check prevents a user from truncating a partial set of tables that would otherwise leave some constraints in an invalid state.
6. The script is meant for use with tables located within the user schema. Constraints created by other users holding REFERENCES privileges cannot be seen and they will cause ORA-02266 errors. The programs shows a warning when it finds that such privileges have been granted to other users on the relevant tables.
   
7. EXECUTE privilege on package DBMS_UTILITY is normally granted to PUBLIC at database installation time, so the procedure should compile without problems.

See message translations for ORA-02266 and search additional resources.

---

ORA-02266: La tabella referenziata da chiavi esterne abilitate dispone di chiavi uniche/primarie
ORA-02266: claves únicas/primarias en la tabla referidas por claves ajenas activadas
ORA-02266: les claus úniques/primàries de la taula estan referenciades per claus foranes activades
ORA-02266: Les clés primaires/uniques de la table référencées par des clés étrangères
ORA-02266: Eindeutige/primäre Schlüssel in Tabelle, auf die von aktivierten Fremdschlüsseln referenziert wird.
ORA-02266: μοναδικά/πρωτεύοντα κλειδιά σε πίνακα αναφέρονται από ενεργοποιημένα ξένα κλειδιά
ORA-02266: tabel har unikke/primære nøgler, der refereres til af aktiverede fremmede nøgler
ORA-02266: unika/primära nycklar i tabellen refereras via aktiverade främmande nycklar
ORA-02266: entydige/primære nøkler i tabellen refereres av aktiverte fremmednøkler
ORA-02266: käyttöön otetut viiteavaimet viittaavat taulun yksilöiviin tai perusavaimiin
ORA-02266: a táblának van olyan egyedi/elsődleges kulcsa, melyre engedélyezett külső kulcs hivatkozik
ORA-02266: cheile unice/primare din tabelă sunt referite de cheile externe activate
ORA-02266: Geactiveerde verwijzende sleutels verwijzen naar unieke/primaire sleutels in tabel.
ORA-02266: chaves exclusiva/primária na tabela referenciadas por chaves externas ativadas
ORA-02266: chaves exclusivas/primárias referenciadas por chaves externas activadas
ORA-02266: на уник./перв. ключи в таблице ссылаются вторичные ключи
ORA-02266: v tabulce existují unikátní/primární klíče odkazované odblokovanými cizími klíči
ORA-02266: jednoznačné/primárne kľúče v tabuľke sú odkazované aktivovanými cudzími kľúčmi
ORA-02266: tabela ma unikatowe/główne klucze do których odwołują się obce klucze
ORA-02266: tabloda etkin yabancı anahtarlar tarafından başvurulan benzersiz/birincil anahtarlar var