ORACLE - problem: Klucz obcy z klauzulami "ON DELETE CASCADE" oraz "ON UPDATE CASCADE"

Witam,

mam pytanie do osób znających bazy danych oracle. Na prywatne potrzeby analiz kursów akcji kilka lat temu stworzyłem i do tej pory rozwijam małą bazę danych (800 MB) na MSSQL Serwer Developer. Z przyczyn technicznych noszę się z zamiarem migracji na bazę Oracle (wersja Express). Mój plan jest następujący stworzyć identyczną strukturę bazy na platformie oracle jak na MSSQL Serwer, export-import danych i kontynuować dalsze analizy już tylko na Oracle. Ponieważ jestem samoukiem wiedziałem, że na początku będzie ciężko, ale żeby wyłożyć się na tak prostej kwestii jak zależności klucza obcego między dwoma tabelami, to tego nawet nie brałem pod uwagę.

A więc do rzeczy, o co chodzi.

mam dwie tabele "Slownik" i "Notowania". Każda z nich zawiera kolumnę "Symbol",
W tabeli "Slownik" kolumna "Symbol" jest kluczem jednokolumnowym kluczem głównym (Primary Key).
W tabeli "Notowania" kolumna "Symbol" jest częścią dwukolumnowego klucza głównego (Primary Key) DataSesji,Symbol

Chciałem utworzyć klucz obcy w tabeli "Notowania" w taki sposób, aby każda zmiana lub usunięcie czegokolwiek w kolumnie "Symbol" tabeli "Słownik" automatycznie aktualizowała odpowiedni wiersz w kolumnie "Symbol" tabeli "Notowania" (Tak mam w MSSQL Serwer).

Kod w Oracle, który miałby to robić wygląda następująco:

ALTER TABLE NOTOWANIA_GPW 
ADD CONSTRAINT  FK_NOTOWANIA_GPW 
FOREIGN KEY (SYMBOL)
REFERENCES SLOWNIK (SYMBOL) 
  ON DELETE CASCADE
  ON UPDATE CASCADE;

niestety, jego uruchomienie generuje następujący komunikat błędu:

Error (6,1): ORA-01735: invalid ALTER TABLE option

Gdy usunę TYLKO OSTATNIĄ linię powyższego kodu, wszystko jest OK. Na stronie Oracle znalazłem składnie "FOREIGN KEY Constraints" i wynika z niej, że obydwie klauzule "ON DELETE" i "ON UPDATE" z opcją "CASCADE" są obsługiwane.

Źródło: link

Pytanie, gdzie popełniam błąd w kodzie oracle, że nie mogę utworzyć opisanej zależności lub czy to możliwe, że Oracle naprawdę obsługuję TYLKO klauzulę "ON DELETE CASCADE" a klauzuli "ON UPDATE CASCADE" nie?

Z góry dziękuje za każdą formę pomocy.

Pozdrawiam
Dyktat

To może znajdź dokumentację do bazy Oracle a nie MySQL którą tutaj wkleiłeś

A jaki jest sens aktualizacji symbolu - jak rozumiem instrumentu finansowego - w przypadku jego zmiany? Jeśli symbol jest kluczem głównym, to jego zmiana nie powinna być dozwolona.
W takich przypadkach wygasza się stary symbol (ustawia status, datę, powód wygaśnięcia itp.) i wstawia nowy, który go zastępuje. Wtedy zachowujesz historię notowań dla starego symbolu.

Jeśli faktycznie widzisz potrzebę takiej zmiany, to po prostu zrób to na kluczach sztucznych a nie naturalnych. Czyli w SLOWNIK dodaj jakieś unikalne ID jako klucz główny, a w NOTOWANIA_GPW kolumnę SLOWNIK_ID jako klucz obcy. I wtedy możesz sobie zmieniać kody słownika jak chcesz.

Ale tak jak napisałem, automatyczne aktualizowanie czy wręcz usuwanie danych rzadko kiedy ma biznesowe uzasadnienie. Przede wszystkim niszczysz historię tego obiektu, a w biznesie to jak w relacjach z ludźmi - przeszłość jest ważna.

robertos7778 napisał(a):

A jaki jest sens aktualizacji symbolu - jak rozumiem instrumentu finansowego - w przypadku jego zmiany? Jeśli symbol jest kluczem głównym, to jego zmiana nie powinna być dozwolona.
W takich przypadkach wygasza się stary symbol (ustawia status, datę, powód wygaśnięcia itp.) i wstawia nowy, który go zastępuje. Wtedy zachowujesz historię notowań dla starego symbolu.

Jeśli faktycznie widzisz potrzebę takiej zmiany, to po prostu zrób to na kluczach sztucznych a nie naturalnych. Czyli w SLOWNIK dodaj jakieś unikalne ID jako klucz główny, a w NOTOWANIA_GPW kolumnę SLOWNIK_ID jako klucz obcy. I wtedy możesz sobie zmieniać kody słownika jak chcesz.

Ale tak jak napisałem, automatyczne aktualizowanie czy wręcz usuwanie danych rzadko kiedy ma biznesowe uzasadnienie. Przede wszystkim niszczysz historię tego obiektu, a w biznesie to jak w relacjach z ludźmi - przeszłość jest ważna.

Witam,

"A jaki jest sens aktualizacji symbolu - jak rozumiem instrumentu finansowego - w przypadku jego zmiany?"

• często się zdarza, że organa spółki podejmują decyzję o zmianie nazwy a tym samym symbolu spółki (z tylko dla nich znanych powodów) ale de facto to ten sam podmiot i jego notowania "obowiązują", dlatego taka funkcjonalność jest bardzo użyteczna.

"Jeśli faktycznie widzisz potrzebę takiej zmiany, to po prostu zrób to na kluczach sztucznych a nie naturalnych. Czyli w SLOWNIK dodaj jakieś unikalne ID jako klucz główny, a w NOTOWANIA_GPW kolumnę SLOWNIK_ID jako klucz obcy. I wtedy możesz sobie zmieniać kody słownika jak chcesz."

• możesz wytłumaczyć czym mój przypadek różni się od rozwiązania, które opisałeś, czy to co do zasady nie jest tym samym?

Stworzyłem na takich kolumnach klucz główny w tab. "Notowania" aby zabezpieczyć się przed pojawieniem się duplikatów kombinacji Symbol-DataSesji a żeby nie tworzyć dodatkowych ograniczeń. Jeśli w bazie Oracle nie jest możliwe takie rozwiązanie, to skorzystam z twojej rady i stworzę klucze sztuczne w obu tabelach.

Ale chciałbym wrócić do mojego pytania, czy Oracle naprawdę obsługuję TYLKO klauzulę "ON DELETE CASCADE" a klauzuli "ON UPDATE CASCADE" nie?

Dziękuje za szybką pomoc w rozwiązaniu mojego problemu

Pozdrawiam
Dyktat

ralf napisał(a):

To może znajdź dokumentację do bazy Oracle a nie MySQL którą tutaj wkleiłeś

Wkleiłem ten link ponieważ to efekt wyszukiwarki ze strony Oracle Polska , "Zasoby -> Dokumentacja popularnych produktów -> Bazy danych -> Database Documentation" - wpisane hasło to: "foreign key syntax". Jeśli to nie zły odnośnik, to przepraszam - nie mam jak na razie zbyt dużego doświadczenia z Oraclem.
Jeśli mógłbyś wkleić link do właściwej dokumentacji Oracle w której będzie pełna składnia, będę bardzo wdzięczny.

Z góry dziękuje.

Pozdrawiam
Dyktat

https://www.techonthenet.com/oracle/foreign_keys/foreign_keys.php
https://www.oracletutorial.com/oracle-basics/oracle-foreign-key/

Generalnie używanie czegoś co się może zmienić jako klucza głównego to mocno średni pomysł, niezależnie od tego jakiej bazy używasz. Jeśli potrzebujesz mieć kolumnę z unikalnymi wartościami to tworzysz na niej indeks unikalny o sprawa załatwiona. Poza tym może się zdarzyć, że dodajesz rekord z PK = A, potem zmieniasz jego wartość na B i dodajesz kolejny rekord z kluczem A. Niby jest to poprawny flow ale jeśli masz jakikolwiek system zewnętrzny, który czyta twoje dane to najprawdopodobniej go wykoleiłeś. To co chcesz osiągnąć da się zaimplementować pewnie w 99% baz SQLowych, które można znaleźć na rynku.
Jeszcze raz - używanie pola, które może zmienić swoją wartość w czasie jako klucz główny jest proszeniem się o problemy.

doc oracle jest tutaj
https://docs.oracle.com/en/database/oracle/oracle-database/21/sqlrf/constraint.html#GUID-1055EA97-BA6F-4764-A15F-1024FD5B6DFE

https://docs.oracle.com/en/ - punkt wejściowy do dokumentacji. Tam można wybrać obszar produktowy, produkt, wersję produktu, obszar w ramach produktu.

Przykładowo:

• bazy danych -> https://docs.oracle.com/en/database/index.html
• dalej silnik -> Oracle Database, Oracle NoSQL, MySQL, TimesTen, RDB, ...

Dziękuje wszystkim za każdą pomoc dotyczącą mojego zagadnienia. Pogrzebałem trochę w sieci i chyba udało mi się znaleźć odpowiedź na moje pytanie odnośnie obsługi przez Oracle klauzuli "ON UPDATE" - otóż NIE.

A swoją drogą wklejam link do bardzo interesującej dyskusji na ten temat.
link

Szkoda bo to bardzo komplikuje moje plany migracji na tą bazę danych. W moim przypadku jakość danych to fundament moich analiz. Z drugiej strony zagłębiłem się w możliwości tej platformy są imponujące, wiem, że zaawansowani użytkownicy są w stanie napisać odpowiednie triggery, które pozwolą uzyskać opisaną użyteczność, ale dla mnie jest to na razie nieosiągalne. Postanowiłem jednak zagłębić się w technologię i możliwości bazy Oralce bo ich potencjał jest olbrzymi i zrobiły na mnie wrażenie - może to kiedyś zaowocuje. Jednak na chwilę obecną jest (1:0) w setach dla MSSQL Serwer, ale mecz się jeszcze nie skończył.

Pozdrawiam wszystkich
Dyktat.

@dyktat: Spróbuj z postgresem, wygląda, że ma to czego potrzebujesz https://www.postgresql.org/docs/current/ddl-constraints.html#DDL-CONSTRAINTS-FK
Ma bardzo ciekawe funkcjonalności i spore tempo rozwoju. Bardzo dobra alternatywa dla silnika Oracle.

Mam wrażenie, że za bardzo skupiłeś się na swojej wersji rozwiązania, że wybierasz bazę pod kątem wspierania dość niszowej funkcji. Automatyczna modyfikacja danych przez bazę po ich zmianie jest mało transparentna i aplikacja nie ma żadnego wpływu na działanie tej funkcji.

Wystarczy, że użyłbyś sztucznego klucza słownikowego (a nie prawdziwego symbolu), a dodatkowo utworzył proste mapowanie z tego kodu na rzeczywiste kody spółek.
MAPOWANIE (SYMBOL, RZECZYWISTY_KOD, DATA_OD, DATA_DO, POWOD_ZMIANY), z kluczem unikalnym na RZECZYWISTY_KOD, SYMBOL.
Pozostałe tabele bez zmian.
Masz np. SYMBOL=XYX123, który jest też kluczem obcym w innych tabelach.

Do MAPOWANIE wstawiasz inicjalnie wszystkie spółki:
XYZ123, ACT
XYZ124, ACG
XYZ125, AGO

Jeśli jakaś spółka zmieni kod, dodajesz nowy rekord z mapowaniem:
XYZ125, AGO
XYZ125, AG1

Przy wyszukiwaniu danych dotyczących danej spółki najpierw wyszukujesz jej w MAPOWANIE, pobierasz jej SYMBOL i używasz go w innych zapytaniach (tam gdzie jest on kluczem obcym).