Oracle PL/SQL - Klucz obcy - Foreign Key

Klucz obcy stosuje w sytuacjach gdy chcemy zabezpieczyć integralność danych znajdujących powiązanych tabelach.

Np. mamy dwie tabele: jedną z osobami, drugą z telefonami tych osób. Wiadomo, że każdy może mieć dowolną liczbę telefonów więc opłaca się rozbić te dane na dwie tabele. Jednak co będzie jeśli ktoś spróbuje skasować rekord z osobą. Pojawią się telefony nie przypisane nikomu. Klucze mają pomóc "ogarniać" prawie wszystkie możliwe sytuacje.

Kluczy obcych w pojedynczej tabeli może być w przeciwieństwie do klucza głównego więcej niż jeden - im jest ich więcej tym lepiej gdyż są bardo skuteczną linią obrony przed niesfornymi osobnikami kasującymi dane.

Tworzenie klucza klucza obcego (foreign key) na istniejącej tabeli

create table osoba(
	id number primary key,
	nazwisko varchar2(50 CHAR));

create table telefon(
	tel number primary key,
	id number,
	telefon varchar2(20 CHAR));

alter table telefon add constraint telefon_fk foreign key (id) references osoba(id);

Dodawanie klucza foreign key przy tworzeniu tabeli

Klucz obcy - foreign key - można podobnie jak klucz główny - prmiary key - zakładać już przy tworzeniu tabeli.

create table telefon(
  tel number constraint telefon_pk primary key,
  id number,
  telefon varchar2(20 CHAR),
  constraint telefon_fk1 foreign key (id) references osoba(id)
  );

można też nieco krócej:

create table telefon(
 tel number constraint telefon_pk primary key,
 id number constraint telefon_fk1 references osoba(id),
 telefon varchar2(20 CHAR)
 );

Dość ciekawym elementem jaki tu wprowadziłem jest tworzenie klucza głównego o z góry określonej nazwie. Można oczywiście zrobić to jak w poprzednim przykładzie, ale wtedy w widokach systemowych:

select * from user_constraints; -- all_constraints -- dba_constraints

znajdziemy wpisy o podobne do SYS_C00105. Nie są one łatwe w zapamiętaniu więc na dalszym etapie programowanie może się tak zdażyć że będziemy poświęcać całkiem sporo czasu na pozyskiwanie tych nazw.

Usuwanie danych z tabel powiązanych kluczem obcym

Wracając do głównego wątku czyli klucza obcego:

begin
insert into osoba values (1, 'Kowalski');
insert into telefon values (1, 1, '123');
insert into telefon values (2, 1, '324');
end;

Spróbujmy teraz kasować coś z tabeli osoba

delete from osoba where id=1;

Pojawia się komunikat o błędzie w stylu

ORA-02292: integrity constraint (HR.TELEFON_FK) violated - child record found

Żeby w/w delete zadziałał najpierw musielibyśmy skasować wszystkie wpisy z tabeli telefon odpowiadające danej osobie o id=1.

Modyfikacje klucza foreign key

Oczywiście ktoś może użyć opcji cascade jednak wymaga to interwencji administratora

alter table telefon drop constraint telefon_fk;

alter table telefon add constraint telefon_fk foreign key (id) 
references osoba(id) on delete cascade;

Teraz wcześniejsze polecenie delete zostanie wykonane bez błędu - jednak z tabeli telefony znikną oba wpisy odpowiadające Kowalskiemu. Konkluzja jest prosta - opcja cascade jest na tyle niebezpieczna, że nie należy jej używać. Jeśli bowiem na bazie znajdują się cykle to możemy skasować sobie z "pół bazy" jednym małym delete-em.

Od razu wyjaśnię na przykładzie 2 tabel jak wygląda cykl na bazie danych:

create table t1( id1 number primary key, id2 number);
create table t2( id1 number primary key, id2 number);

alter table t1 add constraint t1_fk foreign key (id2) references t2(id1);
alter table t2 add constraint t2_fk foreign key (id2) references t1(id1);

A więc dane w każdej z tabel pośrednio odwołują się do swojej własnej tabeli. Na pierwszy rzut oka wygląda, że jest to bez sensu bo nie da się tam dodawać danych (usuwać zresztą podobnie). Dość obszernie opisałem podobny problem w artykule: import danych.

Wracając do tematu czyli opcji "on delete cascade" - sensowniejszym moim zdaniem rozwiązaniem wydaje się usuwanie wpisu samego wpisu referencyjnego zamiast całego wiersza

alter table telefon add constraint telefon_fk foreign key (id) 
references osoba(id) on delete set null;

Można to wykonać o ile kolumna z kluczem obcym ma zaznaczoną opcję "nullable". Gdy tworzy się tabele nie dodając modyfikatora not null przy nazwach kolumn to domyślnie kolumny będą "nullable". Jeśli było inaczej można spróbować:

alter table telefon modify id null;

Istnieją jeszcze dwa alternatywne sposoby postępowania dla w/w problemu

Złożony klucz obcy (wielopolowy)

W analogiczny spsób można budować również wielopolowe klucze obce:

create table osoba(
  id1 number,
  id2 number,
  nazwisko varchar2(50 CHAR),
  constraint osoba_pk primary key(id1,id2) -- (*)
);

-- (*) lub ewentualnie: 
-- alter table osoba add constraint osoba_pk primary key (id1, id2) ;
  
create table telefon(
  tel number constraint telefon_pk primary key,
  id1 number,
  id2 number,
  telefon varchar2(20 CHAR),
  constraint telefon_fk1 foreign key (id1,id2) references osoba(id1, id2)
  );

Jedynym warunkiem jest założenie klucza unique na wiązanych kolumnach w tabeli osoba

Foreign key

Klucz obcy musi pokazywać na unikalną kolumnę. Oczywiście jeśli pokazuje na kolumnę z kluczem primary key warunek ten jest również zachowany ponieważ primary key to połączenie kluczy unique i not null.

2010.03.28 11:48:36.