Nie projektuję schematów codziennie, ale kiedy to robię, próbuję poprawnie skonfigurować aktualizacje kaskadowe / usunąć, aby ułatwić administrację. Rozumiem, jak działają kaskady, ale nigdy nie pamiętam, który stół jest który.

Na przykład, jeśli mam dwie tabele - Parenti Child- z kluczem obcym do Childtych referencji Parenti ma ON DELETE CASCADE, które rekordy wyzwalają kaskadę, a które rekordy są kasowane? Moim pierwszym przypuszczeniem byłoby, że Childrekordy zostaną usunięte po Parentich usunięciu, ponieważ Childrekordy zależą od Parentakt, ale ON DELETEjest to niejednoznaczne; może to oznaczać usunięcie Parentrekordu po jego Childusunięciu lub może oznaczać usunięcie Childrekordu po jego Parentusunięciu. Więc co to jest?

Chciałbym, aby składnia była ON PARENT DELETE, CASCADE, ON FOREIGN DELETE, CASCADElub coś podobnego, aby usunąć dwuznaczność. Czy ktoś ma jakieś mnemoniki, żeby o tym pamiętać?

Jeśli lubisz Parenti Childwarunki i czujesz, że są one łatwe do być zapamiętany, może chcesz tłumaczenie ON DELETE CASCADEdoLeave No Orphans!

Co oznacza, że ​​kiedy Parentwiersz zostanie usunięty (zabity), żaden wiersz osierocony nie powinien pozostać przy życiu w Childtabeli. Wszystkie dzieci w wierszu nadrzędnym również są zabijane (usuwane). Jeśli którekolwiek z tych dzieci ma wnuki (w innej tabeli za pomocą innego klucza obcego) i jest ON DELETE CASCADEzdefiniowane, należy je również zabić (i wszystkich potomków, o ile zdefiniowano efekt kaskady).

Samo FOREIGN KEYograniczenie można również opisać jako Allow No Orphans!(w pierwszej kolejności). Nie Childpowinno być nigdy dozwolone (zapisywane) w tabeli podrzędnej, jeśli nie ma Parent(wiersza w tabeli nadrzędnej).

Aby ON DELETE RESTRICTzachować spójność, można je przetłumaczyć na (mniej agresywne) You Can't Kill Parents!Tylko wiersze bezdzietne mogą zostać zabite (usunięte).

Na przykład, jeśli mam dwie tabele - Parent i Child - gdzie rekordy Child są własnością rekordów Parent, to która tabela potrzebuje ON DELETE CASCADE?

ON DELETE CASCADE jest klauzulą ​​opcjonalną w deklaracji klucza obcego. To idzie w parze z deklaracją klucza obcego. (Czyli w tabeli „podrzędnej”).

... może to oznaczać usunięcie rekordu nadrzędnego po usunięciu rekordu podrzędnego lub może oznaczać usunięcie rekordu nadrzędnego po usunięciu elementu nadrzędnego. Więc co to jest?

Jednym ze sposobów interpretacji deklaracji klucza obcego jest: „Wszystkie prawidłowe wartości dla tej kolumny pochodzą z„ that_column ”w„ that_table ”.” Kiedy usuwasz wiersz w tabeli „potomnej”, nikogo to nie obchodzi. Nie wpływa to na integralność danych.

Kiedy usuwasz wiersz z tabeli „nadrzędnej” - z „tej_tabeli” - usuwasz prawidłową wartość z możliwych wartości dla tabeli „podrzędnej”. Aby zachować integralność danych, musisz coś zrobić w tabeli „podrzędnej”. Kaskadowe usuwanie jest jedną rzeczą, którą możesz zrobić.

SQL: 2011 Spec

Istnieje pięć opcji ON DELETE, ON UPDATEktóre mogą mieć zastosowanie do FOREIGN KEY. Są one wywoływane <referential actions>bezpośrednio ze specyfikacji SQL: 2011

• ON DELETE CASCADE: jeśli wiersz odnośnej tabeli zostanie usunięty, wszystkie pasujące wiersze w tabeli odnośnika zostaną usunięte.
• ON DELETE SET NULL: jeśli wiersz tabeli, do której istnieje odwołanie, zostanie usunięty, wówczas wszystkie kolumny odnośników we wszystkich pasujących wierszach tabeli odniesień mają wartość NULL.
• ON DELETE SET DEFAULT: jeśli wiersz odnośnej tabeli zostanie usunięty, wówczas wszystkie kolumny odnośników we wszystkich pasujących wierszach tabeli odnośników mają zostać ustawione na wartość domyślną kolumny.
• ON DELETE RESTRICT: zabrania się usuwania wiersza tabeli, do której istnieje odniesienie, jeśli wiersz ten ma pasujące wiersze w tabeli odwołań.
• ON DELETE NO ACTION (domyślnie) : nie ma referencyjnego działania usuwania; ograniczenie referencyjne określa tylko sprawdzenie ograniczenia.

Klucz obcy ustanawia zależność. <referential action>Określa, co się dzieje, gdy związek jest rozpuszczony.

Przykład / metafora / objaśnienie

W tym przykładzie zaakceptujemy wspólny model społeczeństwa i gospodarki: gdzie każda businessjest firmą, która utrzymuje relacje z bourgeoisieprzedsiębiorstwem fatcat_owner.

CREATE TABLE bourgeoisie(
  fatcat_owner varchar(100) PRIMARY KEY
);
INSERT INTO bourgeoisie(fatcat_owner) VALUES
  ( 'Koch Brothers' );

CREATE TABLE business (
  name         varchar(100),
  fatcat_owner varchar(100) REFERENCES bourgeoisie
);
INSERT INTO business(name, fatcat_owner)
  VALUES ('Georgia-Pacific', 'Koch Brothers');

Jeśli wszystkie businesses są bezpośrednio dotknięte bourgeoisieprzez sposób ich fatcat_ownerto co robisz po rewolucji robotniczej, kiedy to Usuniesz fatcat_ownerS i mieć bezklasowe społeczeństwo?

-- Viva la revolución 
BEGIN;
  DELETE FROM bourgeoisie;
END;

Masz tutaj kilka opcji,

• Zatrzymaj rewolucję. W SQL terminologii RESTRICT. Niektórzy uważają, że jest to mniejsze zło, ale zwykle się mylą.

• Pozwól, aby trwało. Jeśli tak, to kiedy nastąpi rewolucja, SQL daje cztery opcje,

  • SET NULL-- zostaw puste. Kto wie, może kapitalizm zostaje przywrócony bourgeoisie, a oligarchowie wypełniają listę fatcat_owners. Ważna uwaga, kolumna musi być NULLABLE(nie NOT NULL), inaczej to się nigdy nie stanie.
  • SET DEFAULT- może już sobie z DEFAULTtym poradziłeś? A DEFAULTmoże wywołać funkcję. Być może twój schemat jest już gotowy na rewolucję.
  • CASCADE- nie ma kontroli uszkodzeń. Jeśli bourgeoisiepójdzie, to też business. Jeśli firma musi mieć fatcat_pig, to czasem sensowniej jest stracić dane, niż mieć inną firmę w businesstabeli.

  • NO ACTION- jest to w zasadzie metoda opóźniania kontroli, w MySQL nie różni się to RESTRICT, ale w PostgreSQL można to zrobić

    -- Not a real revolution.
    -- requires constraint be DEFERRABLE INITIALLY DEFERRED
    BEGIN;
      SET CONSTRAINTS ALL DEFERRED;
      DELETE FROM bourgeoisie;
      INSERT INTO bourgeoisie VALUES ( 'Putin' );
      UPDATE business SET fatcat_pig = 'Putin';
    END;
    

    W takim systemie ograniczenie jest sprawdzane tylko przed zatwierdzeniem transakcji. Może to spowodować zatrzymanie rewolucji, ale możesz odzyskać w transakcji - w pewnym stopniu „odzyskać”.

Byłby prosty mnemonik

PRZY USUNIĘCIU rodzica KASKADA [przez usunięcie] tutaj

To mówi ci, które usunięcia (usunięcia rodzica) są kaskadowane, gdzie idzie instrukcja ON DELETE CASCADE (na potomku), a co zostaje usunięte (potomek).

cóż, być może możemy zracjonalizować składnię. Weźmy przykład w języku Python:

class Parent(self):
    # define parent's fields

class Child(self):    
    # define child's fields
    parent_pk_is_childs_foreign_key = models.ForeignKey(Parent, on_delete=models.CASCADE)

to, co mówi ta linia, to on_delete od rodzica (o czym przypadkowo wspomniano w oświadczeniu), proszę kaskadowo usunąć dziecko. Dlatego instrukcja CASCADE jest zdefiniowana na poziomie potomnym, oznacza te dzieci, które należy usunąć

Na przykład, jeśli miałeś inną klasę

class GrownUpChild(self):    
        # define grown up child's fields
        parent_pk_is_childs_foreign_key = models.ForeignKey(Parent, on_delete=models.DO_NOTHING)

ta struktura wyraźnie pokazuje, które z dzieci należy usunąć (Dziecko), a które mają pozostać (GrownUpChild), choć osierocone

[Edycja: Biorąc pod uwagę kontekst dyskusji, szczególnie w przypadku on_delete = models.CASCADE itp.], W rzeczywistości często pożądanym zachowaniem jest pozostawienie dzieci usuniętego rodzica ze względów kontrolnych i sprawozdawczych, a także odzyskania przypadkowego skreślenia. [oczywiście oprogramowanie na poziomie przedsiębiorstwa zostanie zbudowane wokół takiego zachowania i będzie oznaczać usunięte rekordy jako usunięte = 1 zamiast faktycznie je usuwać, a także nie będzie uwzględniać ich w żadnych zapytaniach dotyczących interfejsu użytkownika, pomijając niektóre specjalnie zaprojektowane raporty. Ponadto będzie miał funkcję usuwania usuniętych == 1 rekordów z bazy danych, co zwykle będzie wykonywane przez administratora interfejsu użytkownika, często unikając jakiegokolwiek zaangażowania ze strony administratora bazy danych.]