Oracle Database 10g - Import danych

Wgrywanie powiązanych kluczami obcymi danych do bazy Oracle

Dodawanie danych do niepowiązanych ze sobą kluczami (więzami integralności) tabel nie jest raczej skomplikowane. Kolejność "insertów" nie jest istotna.

create table tab_a(
d_a number(10) constraint pk_a primary key, 
val varchar2(20)
);

create table tab_b(
id_b number(10) constraint pk_b primary key, 
val varchar2(20)
);

insert into tab_b values (1, 'abb');
insert into tab_a values (1, 'abb');

insert into tab_b values (2, 'abc');
insert into tab_a values (2, 'abc');
commit;

Pierwszy mały "problem" pojawia się, wraz z pojawieniem się pierwszego klucza obcego "fk_2_a" na tabeli tab_b:

drop table tab_a cascade constraints;
drop table tab_b cascade constraints;

create table tab_a(
	id_a number(10) constraint pk_a primary key, 
	val varchar2(20)
);

create table tab_b(
	id_b number(10) constraint pk_b primary key, 
	val varchar2(20)
);

alter table tab_b add constraint fk_2_a 
	foreign key (id_b) references tab_a (id_a) enable;

insert into tab_a values (1, 'abb');
insert into tab_b values (1, 'abb');

insert into tab_a values (2, 'abc');
insert into tab_b values (2, 'abc');
commit;

W tym momencie stała się nagle ważna kolejność dodawanie danych. Najpierw w bazie danych powinny znaleść się wpisy z tabel związanych kluczami obcymi. A więc dane z "tab_a". W ten sposób referencje na tabelę nadrzędną nigdy nie będą wskazywać na null - a wiec nigdy nie zostanie naruszony więz i rzucony wyjątek o treści "parent key not found"

Jeśli spróbujemy to wykonać w kolejności opisanej poniżej zostanie rzucony w/w błąd

drop table tab_a cascade constraints;
drop table tab_b cascade constraints;

create table tab_a(id_a number(10) 
	constraint pk_a primary key, 
	val varchar2(20)
);

create table tab_b(id_b number(10) 
	constraint pk_b primary key, 
	val varchar2(20)
);

alter table tab_b 
	add constraint fk_2_a foreign key (id_b)
	references tab_a (id_a) enable;

insert into tab_b values (1, 'abb');
insert into tab_a values (1, 'abb');
commit;

No dobrze - ale co dzieje się w sytuacji, gdy na bazie znajdują się tabele (grupy tabel) wzajemnie powiązane kluczami (mówi się, że wówczas występują na bazie "cykle"). Nie ma wtedy niestety żadnej możliwości wykonania insertów.

drop table tab_a cascade constraints;
drop table tab_b cascade constraints;

create table tab_a(id_a number(10) constraint pk_a primary key, val varchar2(20));
create table tab_b(id_b number(10) constraint pk_b primary key, val varchar2(20));

alter table tab_b add constraint fk_2_a foreign key (id_b) references tab_a (id_a) enable;
alter table tab_a add constraint fk_2_b foreign key (id_a) references tab_b (id_b) enable;

insert into tab_b values (1, 'abb');
insert into tab_a values (1, 'abb');

insert into tab_a values (2, 'abc');
insert into tab_b values (2, 'abc');
commit;

Są dwa sposoby rozwiązania problemu

Pierwszym jest wyłączenie kluczy obcych by po w pełni zakończonej operacji je włączyć. Niestety istnieje poważna groźba, że dane po zakończeniu wgrywania będę niespójne i klucze się nie włączą. Niby można wtedy stosować sztuczki w stylu alter table tab_b add constraint fk_2_a foreign key (id_b) references tab_a (id_a) enable novalidate;, które dadzą administratorom czas na ręczną naprawę danych. Jednak, praktyka pokazuje, że mało komu będzie się chciało to wykonać. Nie jest to więc szczególnie porządany sposób wyjścia z impasu.

W każdym razie poniżej instrukcja jak to można by było wykonać

drop table tab_a cascade constraints;
drop table tab_b cascade constraints;

create table tab_a(id_a number(10) constraint pk_a primary key, val varchar2(20));
create table tab_b(id_b number(10) constraint pk_b primary key, val varchar2(20));

alter table tab_b add constraint fk_2_a foreign key (id_b) references tab_a (id_a) enable;
alter table tab_a add constraint fk_2_b foreign key (id_a) references tab_b (id_b) enable;

alter table tab_b disable constraint fk_2_a;
alter table tab_a disable constraint fk_2_b;

insert into tab_b values (1, 'abb');
insert into tab_a values (1, 'abb');

insert into tab_b values (2, 'abc');
insert into tab_a values (2, 'abc');
commit;

-- tu krótka modlitwa "żeby się udało... żeby się udało..." ;)
alter table tab_b enable constraint fk_2_a;
alter table tab_a enable constraint fk_2_b;

Na podobne problem jest na szczęście o wiele lepszy sposób. Przestawienie klucza w tryb deferred "odroczony".

drop table tab_a cascade constraints;
drop table tab_b cascade constraints;

create table tab_a(id_a number(10) constraint pk_a primary key, val varchar2(20));
create table tab_b(id_b number(10) constraint pk_b primary key, val varchar2(20));

alter table tab_b add constraint fk_2_a 
	foreign key (id_b) references tab_a (id_a) 
	initially deferred enable;

	alter table tab_a add constraint fk_2_b 
	foreign key (id_a) references tab_b (id_b) 
	initially deferred enable;

insert into tab_b values (1, 'abb');
insert into tab_a values (1, 'abb');

insert into tab_b values (2, 'abc');
insert into tab_a values (2, 'abc');
commit;

Walidacja takiego klcza następuje dopiero w momencie zatwierdzania sesji "commit;".

drop table tab_a cascade constraints;
drop table tab_b cascade constraints;

create table tab_a(id_a number(10) constraint pk_a primary key, val varchar2(20));
create table tab_b(id_b number(10) constraint pk_b primary key, val varchar2(20));

alter table tab_b add constraint fk_2_a 
	foreign key (id_b) references tab_a (id_a) initially deferred enable;
alter table tab_a add constraint fk_2_b 
	foreign key (id_a) references tab_b (id_b) initially deferred enable;

insert into tab_b values (1, 'abb');
--insert into tab_a values (1, 'abb');

insert into tab_b values (2, 'abc');
insert into tab_a values (2, 'abc');

-- wyjątek zostanie rzucony dopiero przy tym commit-ie!
commit;

Ten rodzaj klucza umożliwia podobne "sztuczki" dla wszystkich sessji - może to być niesety niezgodne z założeniami aplikacji. Np gdy wprowadzono obsługę wyjątków podobnych sytuacji (exceptions).

Najlepsze wg mnie rozwiązanie to przestawianie wyłącznie szczególnych sesji (np importy) w tryb deferred.

drop table tab_a cascade constraints;
drop table tab_b cascade constraints;

create table tab_a(id_a number(10) constraint pk_a primary key, val varchar2(20));
create table tab_b(id_b number(10) constraint pk_b primary key, val varchar2(20));

alter table tab_b add constraint fk_2_a 
	foreign key (id_b) references tab_a (id_a) deferrable enable;
alter table tab_a add constraint fk_2_b 
	foreign key (id_a) references tab_b (id_b) deferrable enable;

alter session set constraint = deferred;

insert into tab_b values (1, 'abb');
insert into tab_a values (1, 'abb');

insert into tab_b values (2, 'abc');
insert into tab_a values (2, 'abc');
commit;

Stosując klucze utworzone w trybie deferrable możemy dowolnie wrzucać dane do bazy by pod koniec sesji wszystko zwalidować i w razie konieczności zastosować algorytmy naprawcze

drop table tab_a cascade constraints;

create table tab_a(id_a number(10) constraint pk_a primary key deferrable, val varchar2(20));

alter session set constraint = deferred;

insert into tab_a values (1, 'a');
insert into tab_a values (1, 'b');
insert into tab_a values (1, 'c');

select * from tab_a order by id_a;

-- kod walidująco/naprawczy
select * from tab_a a 
	where a.rowid < any (select a2.rowid from tab_a a2 where a.id_a = a2.id_a);

delete from tab_a a
 where a.rowid < any (select a2.rowid from tab_a a2 where a.id_a = a2.id_a);
-- zostanie wyłącznie ostatni insert z id_a = 1!

commit;

select * from tab_a order by id_a;

PS. Z(nie/a)chęcam do testowania na produkcji... ;p

2010.03.28 11:48:34.