Tuesday, July 10, 2007

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

No comments:

yes you can!

Two great ways to help us out with a minimal effort. Click on the Google Plus +1 button above or...
We appreciate your support!

latest articles