Kiedy / dlaczego używać kaskadowania w SQL Server?

Podczas konfigurowania kluczy obcych w programie SQL Server, w jakich okolicznościach należy je kaskadowo usuwać lub aktualizować i jakie jest tego przyczyny?

Prawdopodobnie dotyczy to również innych baz danych.

Szukam przede wszystkim konkretnych przykładów każdego scenariusza, najlepiej od kogoś, kto z powodzeniem je wykorzystał.

Podsumowanie tego, co do tej pory widziałem:

• Niektórzy w ogóle nie lubią kaskadowania.

Usuń kaskadowo

• Cascade Delete może mieć sens, gdy semantyka relacji może obejmować wyłączność opis „jest częścią ”. Na przykład rekord OrderLine jest częścią jego zamówienia nadrzędnego, a OrderLines nigdy nie będzie współdzielony między wieloma zamówieniami. Gdyby Zakon miał zniknąć, Linia Zamówień również powinna, a linia bez Zakonu byłaby problemem.
• Kanonicznym przykładem usuwania kaskadowego jest SomeObject i SomeObjectItems, w przypadku których nie ma sensu istnienie rekordu elementów bez odpowiedniego rekordu głównego.
• Zalecana nie używać Cascade Usuń jeśli zachowując historię lub za pomocą „miękkie / logiczny Delete” gdzie można ustawić tylko usuniętą kolumnę bitowej do 1 / true.

Aktualizacja kaskadowa

• Aktualizacja kaskadowa może mieć sens, gdy używasz prawdziwego klucza zamiast klucza zastępczego (kolumna tożsamości / autoinkrementacji) między tabelami.
• Kanoniczny przykład Cascade Update to sytuacja, w której masz zmienny klucz obcy, taki jak nazwa użytkownika, którą można zmienić.
• Powinieneś nie używać Cascade Aktualizacja z kluczami, które są osobistego / autoIncrement kolumny.
• Aktualizacji kaskadowej najlepiej używać w połączeniu z unikalnym ograniczeniem.

Kiedy używać kaskadowania

• Możesz chcieć uzyskać bardzo silne potwierdzenie od użytkownika przed zezwoleniem na kaskadowanie operacji, ale zależy to od aplikacji.
• Kaskadowanie może spowodować kłopoty, jeśli źle skonfigurujesz klucze obce. Ale powinno być dobrze, jeśli zrobisz to dobrze.
• Nie jest mądrze używać kaskadowania, zanim dokładnie go zrozumiesz. Jest to jednak przydatna funkcja i dlatego warto poświęcić trochę czasu na jej zrozumienie.

Klucze obce to najlepszy sposób na zapewnienie referencyjnej integralności bazy danych. Unikanie kaskad z powodu bycia magicznym jest jak pisanie wszystkiego w asemblerze, ponieważ nie ufasz magii stojącej za kompilatorami.

Złe jest niewłaściwe użycie kluczy obcych, na przykład tworzenie ich wstecz.

Przykład Juana Manuela jest kanonicznym przykładem, jeśli używasz kodu, istnieje znacznie więcej szans na pozostawienie fałszywych elementów DocumentItem w bazie danych, które przyjdą i cię ugryzą.

Aktualizacje kaskadowe są przydatne, na przykład, gdy masz odniesienia do danych przez coś, co może się zmienić, powiedzmy, że kluczem podstawowym tabeli użytkowników jest kombinacja nazwy i nazwiska. Następnie chcesz, aby zmiany w tej kombinacji były propagowane wszędzie tam, gdzie się do nich odnoszą.

@Aidan, Ta przejrzystość, do której się odnosisz, wiąże się z wysokimi kosztami, szansą pozostawienia fałszywych danych w Twojej bazie danych, która nie jest mała . Dla mnie to zwykle brak znajomości DB i niemożność znalezienia, które FK są na miejscu przed rozpoczęciem pracy z DB, wywołują ten strach. Albo to, albo ciągłe nadużywanie kaskady, używanie jej tam, gdzie byty nie były konceptualnie powiązane, albo gdzie trzeba zachować historię.

Nigdy nie używam usuwania kaskadowego.

Jeśli chcę, aby coś zostało usunięte z bazy danych, chcę wyraźnie powiedzieć bazie danych, co chcę wyprowadzić.

Oczywiście są to funkcje dostępne w bazie danych i mogą się zdarzyć sytuacje, w których można ich użyć, na przykład jeśli masz tabelę zamówień i tabelę orderItem, możesz chcieć wyczyścić pozycje po usunięciu zamówienie.

Podoba mi się przejrzystość, którą uzyskuję, robiąc to w kodzie (lub procedurze składowanej), a nie `` magia ''.

Z tego samego powodu nie jestem fanem wyzwalaczy.

Należy zwrócić uwagę na to, że jeśli usuniesz „zamówienie”, otrzymasz raport „dotyczy 1 wiersza”, nawet jeśli kaskadowe usunięcie usunęło 50 „orderItem's”.

Dużo pracuję z kaskadowym usuwaniem.

Dobrze jest wiedzieć, że ktokolwiek pracuje z bazą danych, może nigdy nie pozostawić niechcianych danych. Jeśli zależności rosną, po prostu zmieniam ograniczenia w diagramie w Management Studio i nie muszę dostosowywać SP ani dataacces.

To powiedziawszy, mam 1 problem z kaskadowym usuwaniem i to jest cykliczne odwołania. Często prowadzi to do części bazy danych, które nie mają usuwania kaskadowego.

Dużo pracuję nad bazą danych i rzadko uważam usuwanie kaskadowe za przydatne. Jedynym razem, gdy korzystałem z nich skutecznie, było to w bazie danych raportów, która jest aktualizowana przez nocną pracę. Upewniam się, że wszelkie zmienione dane są prawidłowo importowane, usuwając wszelkie rekordy najwyższego poziomu, które uległy zmianie od czasu ostatniego importu, a następnie ponownie importuję zmodyfikowane rekordy i wszystko, co się z nimi wiąże. Dzięki temu nie muszę pisać wielu skomplikowanych usunięć, które wyglądają od dołu do góry mojej bazy danych.

Nie uważam usuwania kaskadowego za tak złe, jak wyzwalacze, ponieważ usuwają tylko dane, wyzwalacze mogą zawierać wszelkiego rodzaju nieprzyjemne rzeczy.

Ogólnie rzecz biorąc, całkowicie unikam prawdziwego usuwania i używam usuwania logicznego (tj. Posiadającego kolumnę bitową o nazwie isDeleted, która jest ustawiana na true).

Jednym z przykładów jest sytuacja, w której istnieją zależności między jednostkami ... tj .: Document -> DocumentItems (kiedy usuwasz dokument, DocumentItems nie mają powodu do istnienia)

Użyj usuwania kaskadowego, gdzie chcesz, aby rekord z FK został usunięty, jeśli usunięto powiązany z nim rekord PK. Innymi słowy, gdy rekord jest bez znaczenia bez odniesienia do rekordu.

Uważam, że usuwanie kaskadowe jest przydatne, aby upewnić się, że martwe odwołania są usuwane domyślnie, a nie powodować wyjątków zerowych.

ON Usuń kaskadę:

Gdy chcesz usunąć wiersze w tabeli podrzędnej Jeśli odpowiedni wiersz zostanie usunięty w tabeli nadrzędnej.

Jeśli usuwanie kaskadowe nie jest używane, zostanie zgłoszony błąd dla integralności referencyjnej .

Kaskada aktualizacji ON:

Gdy chcesz, aby zmiana klucza podstawowego została zaktualizowana w kluczu obcym

Słyszałem o administratorach baz danych i / lub „zasadach firmy”, które zabraniają używania funkcji „On Delete Cascade” (i innych) wyłącznie z powodu złych doświadczeń z przeszłości. W jednym przypadku facet napisał trzy wyzwalacze, które w końcu zadzwoniły do ​​siebie. Trzy dni na powrót do zdrowia zaowocowały całkowitym zakazem wyzwalaczy, a wszystko to z powodu działań jednego idjita.

Oczywiście czasami potrzebne są wyzwalacze zamiast kaskady „Przy usuwaniu”, na przykład gdy trzeba zachować niektóre dane potomne. Ale w innych przypadkach użycie metody kaskadowej On Delete jest całkowicie poprawne. Główną zaletą kaskady „On Delete” jest to, że obejmuje ona WSZYSTKIE elementy podrzędne; niestandardowa procedura wyzwalania / przechowywania może nie działać, jeśli nie jest poprawnie zakodowana.

Uważam, że deweloper powinien mieć możliwość podjęcia decyzji na podstawie tego, czym jest program i co mówi specyfikacja. Kryterium nie powinien być zakaz dotyczący dywanów oparty na złych doświadczeniach; proces myślowy „Nigdy nie używaj” jest w najlepszym razie drakoński. Za każdym razem należy dokonywać oceny i wprowadzać zmiany wraz ze zmianą modelu biznesowego.

Czy nie o to chodzi w rozwoju?

Jednym z powodów, dla których warto zastosować usuwanie kaskadowe (zamiast robić to w kodzie) jest poprawa wydajności.

Przypadek 1: z kasowaniem kaskadowym

 DELETE FROM table WHERE SomeDate < 7 years ago;

Przypadek 2: bez usuwania kaskadowego

 FOR EACH R IN (SELECT FROM table WHERE SomeDate < 7 years ago) LOOP
   DELETE FROM ChildTable WHERE tableId = R.tableId;
   DELETE FROM table WHERE tableId = R.tableid;
   /* More child tables here */
 NEXT

Po drugie, po dodaniu dodatkowej tabeli podrzędnej z kasowaniem kaskadowym kod w przypadku 1 nadal działa.

Dodałbym tylko kaskadę, w której semantyka relacji jest „częścią”. W przeciwnym razie jakiś idiota usunie połowę twojej bazy danych, gdy:

DELETE FROM CURRENCY WHERE CurrencyCode = 'USD'

Staram się unikać usuwania lub aktualizacji, których nie zażądałem jawnie na serwerze SQL.

Albo poprzez kaskadowanie, albo poprzez użycie wyzwalaczy. Mają tendencję do gryzienia cię w tyłek przez jakiś czas, albo podczas próby wyśledzenia błędu, albo podczas diagnozowania problemów z wydajnością.

Tam, gdzie bym ich użył, to zagwarantowanie spójności przy niewielkim wysiłku. Aby uzyskać ten sam efekt, należałoby użyć procedur składowanych.

Ja, podobnie jak wszyscy inni tutaj, uważam, że usuwanie kaskadowe jest naprawdę tylko marginalnie pomocne (naprawdę nie jest tak dużo pracy, aby usunąć dane, do których istnieją odniesienia w innych tabelach - jeśli jest dużo tabel, po prostu automatyzujesz to za pomocą skryptu), ale naprawdę denerwujące gdy ktoś przypadkowo usunie kaskadowo niektóre ważne dane, które są trudne do przywrócenia.

Jedynym przypadkiem, w którym bym użył, jest sytuacja, gdy dane w tabeli tabeli są w wysokim stopniu kontrolowane (np. Ograniczone uprawnienia) i są aktualizowane lub usuwane tylko w ramach kontrolowanego procesu (takiego jak aktualizacja oprogramowania), który został zweryfikowany.

Usunięcie lub aktualizacja do S, która usuwa wartość klucza obcego znajdującą się w niektórych krotkach języka R, może być obsługiwana na jeden z trzech sposobów:

1. Odmowa
2. Propagacja
3. anulowanie.

Propagacja jest określana jako kaskadowa.

Istnieją dwa przypadki:

‣ Jeśli krotka w S została usunięta, usuń R krotki, które się do niej odnosiły.

‣ Jeśli zaktualizowano krotkę w S, zaktualizuj wartość w R krotkach, które się do niej odwołują.

Jeśli pracujesz na systemie z wieloma różnymi modułami w różnych wersjach, może być bardzo pomocne, jeśli usunięte kaskadowo elementy są częścią / własnością posiadacza PK. W przeciwnym razie wszystkie moduły wymagałyby natychmiastowych poprawek, aby oczyścić ich zależne elementy przed usunięciem właściciela PK, lub relacja klucza obcego zostałaby całkowicie pominięta, prawdopodobnie pozostawiając mnóstwo śmieci w systemie, jeśli czyszczenie nie zostanie wykonane poprawnie.

Właśnie wprowadziłem usuwanie kaskadowe dla nowej tabeli przecięć między dwiema już istniejącymi tabelami (przecięcie tylko do usunięcia), po tym, jak usuwanie kaskadowe było od dłuższego czasu odradzane. Nie jest też źle, jeśli dane zostaną utracone.

Jest to jednak zła rzecz w przypadku tablic list wyliczeniowych: ktoś usuwa wpis 13 - żółty z tabeli "kolory", a wszystkie żółte elementy w bazie danych zostają usunięte. Ponadto czasami są one aktualizowane w sposób usuń wszystko wstaw wszystko, co prowadzi do całkowitego pominięcia integralności referencyjnej. Oczywiście, że to źle, ale jak zmienisz złożone oprogramowanie, które działa od wielu lat, z wprowadzeniem prawdziwej integralności referencyjnej, które jest narażone na nieoczekiwane skutki uboczne?

Innym problemem jest sytuacja, gdy oryginalne wartości klucza obcego powinny być zachowane nawet po usunięciu klucza podstawowego. Można utworzyć kolumnę reliktu i opcję ON DELETE SET NULL dla oryginalnego FK, ale to znowu wymaga wyzwalaczy lub określonego kodu, aby zachować nadmiarową (z wyjątkiem po usunięciu PK) wartość klucza.

Usuwanie kaskadowe jest niezwykle przydatne podczas implementowania logicznych jednostek nadtypów i podtypów w fizycznej bazie danych.

Kiedy oddzielne tabele nadtypów i podtypów są używane do fizycznego wdrażania nadtypów / podtypów (w przeciwieństwie do łączenia wszystkich atrybutów podtypów w pojedynczą fizyczną tabelę nadtypów), istnieje jeden do -Jedna relacja między tymi tabelami a problemem polega na tym, jak utrzymać 100% synchronizację kluczy podstawowych między tymi tabelami.

Usuwanie kaskadowe może być bardzo przydatnym narzędziem do:

1) Upewnij się, że usunięcie rekordu nadtypu powoduje również usunięcie odpowiadającego mu rekordu pojedynczego podtypu.

2) Upewnij się, że usunięcie rekordu podtypu powoduje również usunięcie rekordu nadtypu. Osiąga się to poprzez zaimplementowanie wyzwalacza usuwania „zamiast” w tabeli podtypów, który idzie i usuwa odpowiedni rekord nadtypu, który z kolei kaskadowo usuwa rekord podtypu.

Użycie usuwania kaskadowego w ten sposób gwarantuje, że nie istnieją żadne osierocone rekordy nadtypów lub podtypów, niezależnie od tego, czy najpierw usuniesz rekord nadtypu, czy też rekord podtypu.