Kaskadowe uaktualnienia

Czy użytkownik Twojej aplikacji zażyczył sobie kiedyś, że chce modyfikować wartość w kolumnie, podczas gdy jest na niej zdefiniowane ograniczenie PRIMARY KEY z istniejącymi ograniczeniami referencyjnymi w tabelach podrzędnych?. Poniżej możesz znaleźć wytłumaczenie tego na czym polega problem i jakie są jego alternatywne rozwiązania.

Problem kaskadowego uaktualnienia pewnej wartości pojawia się w sytuacji kiedy podczas projektowania bazy danych zakładaliśmy, że pewien atrybut encji (później kolumna w tabeli) jest unikalny w zbiorze wszystkich wystąpień encji (później wszystkich rekordów w tabeli) oraz, że wartość tego atrybutu nie będzie się zmieniać, a założenie to okazuje się po pewnym czasie niewłaściwe.

Rozważmy hipotetyczny system kadrowy, w którym użytkownik może definiować grupy pracownicze (np. sprzedawcy, administracja, obsługa techniczna), do których należą poszczególni pracownicy. Ze względu na to, że takich grup pracowniczych może być bardzo niewiele, zdefiniowany został atrybut SYMBOL GRUPY typu VARCHAR2(2) pełniący rolę unikalnego identyfikatora encji GRUPA PRACOWNICZA. W bazie danych tabela PRACOWNICY będzie posiadała atrybut (klucz obcy) PR_GR_SYMBOL_GRUPY VARCHAR2(2) CONSTRAINT FK_PR_GR REFERENCES PRACOWNICY.

Załóżmy teraz, że chcemy zmienić symbol dla grupy pracowników obsługi technicznej z wartości „TE” na „TO”. Gdyby system zarządzania bazą danych wspierał kaskadowe uaktualnienia tak jak kaskadowe usuwanie rekordów w tabelach podrzędnych (REFRENCES nazwa_tabeli CASCADE DELETE), nie byłoby problemu, ale skoro tego nie robi to problem istnieje. Standardowe uaktualnienie wartości atrybutu GR_SYMBOL_GRUPY za pomocą instrukcji UPDATE nie uda się ponieważ narusza to ograniczenie referencyjne. Gdyby operacja się udała to pewna liczba pracowników byłaby przypisana do nie istniejącej już grupy „TE”. Uaktualnienie wartości kluczy obcych w tabeli PRACOWNICY też się nie uda gdyż grupa o symbolu „TO” jeszcze nie istnieje. A nasz użytkownik się niecierpliwi i chce zmienić symbol dla pracowników obsługi technicznej…

Istnieje kilka alternatywnych rozwiązań kaskadowego uaktualnienia. Dwa pierwsze z przedstawionych poniżej zakładają oprogramowanie kaskadowego uaktualnienia w aplikacji, natomiast trzecie rozwiązanie zaczerpnięte z Oracle Government and Education rozwiązuje problem na poziomie bazy danych w sposób transparentny dla aplikacji.

Pierwsze rozwiązanie posiada następujący scenariusz:

  1. tworzymy kopię rekordu, który chcemy modyfikować, ale z nową wartością klucza podstawowego
  2. w tabelach podrzędnych uaktualniamy wszystkie klucze obce ze starej wartości na nową
  3. usuwamy rekord ze starą wartością klucza podstawowego

Dla powyższego przykładu wygląda to następująco:

insert into grupy_pracownicze values(gr_symbol_grupy, gr_nazwa, …)
select „TO”, gr_nazwa, … from grupy_pracownicze where gr_symbol_grupy=”TE”;
update pracownicy set pr_gr_symbol_grupy=”TO” where pr_symbol_grupy=”TE”;
delete from grupy_pracownicze where gr_symbol_grupy=”TE”;

W ogólności, operacja update musi być wykonana na wszystkich tabelach, które posiadają klucz obcy wskazujący na uaktualniany przez nas klucz podstawowy. Rozwiązanie to ma tylko jedną wadę, w większości przypadków bez znaczenia, a mianowicie zmianie ulega ROWID „zmodyfikowanego” w ten sposób rekordu. W pewnych zastosowaniach gdy wykorzystujemy ROWID to rozwiązanie takie może się okazać nie do zaakceptowania.

Drugie rozwiązanie polega na chwilowym wyłączeniu ograniczeń referencyjnych. Scenariusz tego podejścia jest następujący:

  1. korzystając z wbudowanej procedury Forms 4.5 o nazwie FORMS_DDL wyłączamy ograniczenia referencyjne w tabelach podrzędnych
  2. uaktualniamy wartość klucza podstawowego i wartości kluczy obcych w tabelach podrzędnych
  3. włączamy ograniczenia referencyjne za pomocą FORMS_DDL

W odniesieniu do powyższego przykładu wyglądałoby to następująco:

forms_ddl(„alter table pracownicy disable constraint fk_pr_gr”);
update grupy_pracownicze set gr_symbol_grupy=”OT” where gr_symbol_grupy=”TE”;
update pracownicy set pr_gr_symbol_grupy=”OT” where gr_symbol_grupy=”TE”;
forms_ddl(„alter table pracownicy enable constraint fk_pr_gr”);

Takie rozwiązanie zapewnia stabilność ROWID, ale ma inne i chyba znacznie poważniejsze wady. Po pierwsze, wykonanie operacji DDL powoduje niejawne zakończenie transakcji, czyli użytkownik nie będzie mógł wycofać wprowadzonych wcześniej zmian. Z tego samego powodu użytkownik nie będzie mógł wycofać operacji modyfikacji wartości klucza podstawowego, którą właśnie wykonał. Po drugie, operacja DDL wymaga wyłącznego dostępu do modyfikowanego obiektu czyli nie uda się jeśli inni użytkownicy uaktualniają w tym czasie którąkolwiek z tabel podrzędnych, a trzeba przyznać, że nawet w tak prostym przykładzie jak powyżej, jest do dosyć prawdopodobne. Sprawdzenie czy operacja wykonana za pomocą FORMS_DDL powiodła się można sprawdzić za pomocą funkcji FORM_SUCCESS lub FORM_FAILURE. Po trzecie, rozwiązania tego nie da się zastosować aplikacji wykonanej za pomocą SQL*Froms 3.0.

Trzecie rozwiązanie, zaczerpnięte z Oracle Government and Education, polega na oprogramowaniu kaskadowych uaktualnień za pomocą procedur i wyzwalaczy na poziomie bazy danych w sposób transparentny dla aplikacji. Oferowany tam skrypt UC.SQL tworzy w bazie danych pakiet o nazwie UPDATE_CASCADE zawierający procedurę ON_TABLE. Jeśli zaistnieje potrzeba wykonywania kaskadowych uaktualnień na jakiejś tabeli to wywołanie UPDATE_CASCADE.ON_TABLE z przekazaną nazwą tabeli oprogramuje w bazie danych kaskadowe uaktualnianie jej klucza podstawowego. Interfejs procedury ON_TABLE jest następujący:

CASCADE_UPDATE.ON_TABLE
Nazwa argumentu Typ In/Out Default?
————————– —– ———- ——–
P_TABLE_NAME VARCHAR2 IN
P_PRESERVE_ROWID BOOLEAN IN DEFAULT
P_USE_DBMS_OUTPUT BOOLEAN IN DEFAULT

Możliwe są różne wywołania procedury, na przykład:

  • exec update_cascade.on_table(„tab1”)

    utworzy pakiet w bazie danych obsługujący w sposób domyślny kaskadowe uaktualnianie na tabeli tab1, w którym ROWID modyfikowanego rekordu zostaje zachowany

  • exec update_cascade.on_table(„tabela”, false)

    utworzony pakiet będzie wspierał kaskadowe uaktualnienia na tabeli tab1, ale bez zachowania ROWID modyfikowanego rekordu, dzięki czemu czasy kaskadowych uaktualnień będą o 1/3 krótsze.

  • exec update_cascade.on_table(„tabela”, false, true)

    zamiast tworzenia pakietu (identyczny jak w poprzednim przykładzie) zostanie on wyświetlony na ekranie.

Niestety, życie byłoby zbyt piękne… to rozwiązanie ma również pewne wady, które mogą się okazać nie do zaakceptowania, a mianowicie: na tabeli nie mogą istnieć inne ograniczenia lub indeksy unikalne poza PRIMARY KEY, tabela i wszystkie tabele podrzędne muszą być w tym samym schemacie i właściciel tabeli musi mieć jawnie nadany przywilej CREATE PROCEDURE i CREATE TRIGGER (poprzez rolę nie wystarczy).

Istnieje jeszcze jedno rozwiązanie problemu kaskadowego uaktualniania. Jest nim odpowiednie zaprojektowanie bazy danych, tak aby w ogóle nie istniała potrzeba kaskadowego uaktualniania kluczy podstawowych. Pamiętajmy, że możemy zawsze dodać do encji sztuczny atrybut pełniący rolę unikalnego identyfikatora. Należy to robić nie tylko w sytuacjach gdy wartość naturalnego unikalnego atrybutu (potencjalnie unikalnego identyfikatora encji) może być modyfikowana, ale również gdy liczba naturalnych atrybutów zapewniających unikalność wynosi 3 lub więcej jego lub gdy rozmiar atrybutu jest zbyt duży aby później używać go jako klucze obce w powiązanych tabelach. Na wyświetlanym w aplikacji atrybucie unikalnym możemy oczywiście zdefiniować ograniczenie UNIQUE i udostępnić go użytkownikowi do swobodnej modyfikacji.


Maciej Matysiak