Różnica między włączoną kaskadą usuwania i kaskadą aktualizacji w mysql

Mam dwie tabele w MySQL od bazy danych parent, child. Próbuję dodać odniesienia do klucza obcego do mojej tabeli podrzędnej na podstawie tabeli nadrzędnej. Czy jest jakaś znacząca różnica między ON UPDATE CASCADEiON DELETE CASCADE

Stolik mojego rodzica

CREATE TABLE parent (
    id INT NOT NULL,
    PRIMARY KEY (id)
) ENGINE=INNODB;

Moje pytanie brzmi: jaka jest różnica między następującymi zapytaniami sql.

1. ON DELETE CASCADE

   CREATE TABLE child (
       id INT, 
       parent_id INT,
       INDEX par_ind (parent_id),
       FOREIGN KEY (parent_id) 
           REFERENCES parent(id)
           ON DELETE CASCADE
   ) ENGINE=INNODB;
   

2. ON UPDATE CASCADE

   CREATE TABLE child (
       id INT, 
       parent_id INT,
       INDEX par_ind (parent_id),
       FOREIGN KEY (parent_id) 
           REFERENCES parent(id)
           ON UPDATE CASCADE
   ) ENGINE=INNODB;
   

3. ON UPDATE CASCADE ON DELETE CASCADE

   CREATE TABLE child (
           id INT, 
           parent_id INT,
           INDEX par_ind (parent_id),
           FOREIGN KEY (parent_id) 
               REFERENCES parent(id)
               ON UPDATE CASCADE ON DELETE CASCADE
       ) ENGINE=INNODB;
   

Czy w zapytaniach są jakieś błędy? Co oznaczają te zapytania (1,2 i 3)? Czy są takie same ???

Bardzo dobry wątek na ten temat można znaleźć tutaj i tutaj . Ostatecznym przewodnikiem dla MySQL jest oczywiście dokumentacja, którą można znaleźć tutaj .

W standardzie SQL 2003 istnieje 5 różnych akcji referencyjnych:

1. KASKADA
2. OGRANICZAĆ
3. BEZ AKCJI
4. ZESTAW NULL
5. USTAW DOMYŚLNIE

Aby odpowiedzieć na pytanie:

1. KASKADA

   • ON DELETE CASCADEoznacza, że ​​jeśli rekord macierzysty zostanie usunięty, wszelkie rekordy potomne również zostaną usunięte. Moim zdaniem nie jest to dobry pomysł. Powinieneś śledzić wszystkie dane, które kiedykolwiek były w bazie danych, chociaż można to zrobić za pomocą TRIGGERs. (Patrz jednak zastrzeżenie w komentarzach poniżej).

   • ON UPDATE CASCADEoznacza, że ​​jeśli nadrzędny klucz podstawowy zostanie zmieniony, wartość podrzędna również ulegnie zmianie, aby to odzwierciedlić. Ponownie moim zdaniem nie jest to świetny pomysł. Jeśli zmieniasz PRIMARY KEYs z dowolną regularnością (lub nawet w ogóle!), Coś jest nie tak z twoim projektem. Ponownie zobacz komentarze.

   • ON UPDATE CASCADE ON DELETE CASCADEoznacza, że ​​jeśli TY UPDATE LUB DELETE rodzic, zmiana jest przekazywana kaskadowo do dziecka. Jest to równoważne z ANDwynikami pierwszych dwóch stwierdzeń.

2. OGRANICZAĆ

   • RESTRICToznacza, że ​​każda próba usunięcia i / lub aktualizacji rodzica nie powiedzie błędu. Jest to domyślne zachowanie w przypadku, gdy akcja referencyjna nie jest wyraźnie określona.

     Dla ON DELETElub ON UPDATEnieokreślonego domyślnym działaniem jest zawsze RESTRICT`.

3. BEZ AKCJI

   • NO ACTION: Z instrukcji . Słowo kluczowe ze standardowego SQL. W MySQL, równoważne z RESTRICT. Serwer MySQL odrzuca operację usuwania lub aktualizacji dla tabeli nadrzędnej, jeśli w odnośnej tabeli znajduje się powiązana wartość klucza obcego. Niektóre systemy baz danych mają odroczone kontrole i NO ACTIONsą odroczone. W MySQL ograniczenia klucza obcego są sprawdzane natychmiast, NO ACTIONpodobnie jak RESTRICT.

4. ZESTAW NULL

   • SET NULL- ponownie z instrukcji. Usuń lub zaktualizuj wiersz z tabeli nadrzędnej i ustaw kolumnę lub kolumny klucza obcego w tabeli podrzędnej na NULL. Nie jest to najlepszy pomysł IMHO, przede wszystkim dlatego, że nie ma możliwości „podróży w czasie” - tj. Spoglądania wstecz w tabele podrzędne i kojarzenia rekordów NULLzs z odpowiednim rekordem nadrzędnym - albo CASCADEużycia TRIGGERs do zapełniania tabel rejestrowania w celu śledzenia zmiany (ale zobacz komentarze).

5. USTAW DOMYŚLNIE

   • SET DEFAULT. Kolejna (potencjalnie bardzo przydatna) część standardu SQL, której MySQL nie zawracał sobie głowy wdrażaniem! Pozwala deweloperowi na określenie wartości, dla której należy ustawić kolumny klucza obcego w UPDATE lub DELETE. InnoDB i NDB odrzucą definicje tabel z SET DEFAULTklauzulą.

Jak wspomniano powyżej, powinieneś poświęcić trochę czasu na przeglądanie dokumentacji tutaj .

Te dwie czynności są odpowiednio wykonywane, gdy odnośny rekord w tabeli nadrzędnej zmieni swój identyfikator i zostanie usunięty.

Jeśli wykonasz:

UPDATE parent SET id = -1 WHERE id = 1;

I istnieje przynajmniej jeden rekord na childz parent_id = 1, 1) nie powiedzie; w przypadkach 2) i 3) wszystkie rekordy o identyfikatorze nadrzędnym = 1 są aktualizowane do identyfikatora nadrzędnego = -1.

Jeśli wykonasz:

DELETE FROM parent WHERE id = 1;

I istnieje przynajmniej jeden rekord na childz parent_id = 1, 2) nie powiedzie; w przypadkach 1) i 3) wszystkie zapisy z parent_id = 1są usuwane.

3) jest poprawny pod względem składniowym.

Pełna dokumentacja znajduje się w instrukcji .

Nie mam wystarczającej reputacji, aby komentować poprzednie odpowiedzi. Pomyślałem więc, że trochę rozwinę.

1) PRZY USUNIĘCIU KASKADA oznacza, że ​​jeśli rekord nadrzędny zostanie usunięty, wówczas wszelkie odniesienia podrzędne zostaną również usunięte. ON UPDATE domyślnie ma wartość RESTRICT, co oznacza, że ​​UPDATE w rekordzie nadrzędnym zakończy się niepowodzeniem.

2) Domyślnie akcja ON DELETE ma wartość RESTRICT, co oznacza, że ​​DELETE w rekordzie nadrzędnym zakończy się niepowodzeniem. PRZY AKTUALIZACJI KASKADA zaktualizuje wszystkie referencyjne rekordy potomne, gdy rekord macierzysty zostanie zaktualizowany.

3) Zobacz działania CASCADE w 1) i 2) powyżej.

W przypadku używania identyfikatorów rekordów nadrzędnych jako kluczy obcych (w tabelach podrzędnych) - doświadczenie mówi a) jeśli identyfikatorami są automatycznie generowane numery sekwencyjne, NIE NALEŻY ich używać jako kluczy obcych. Zamiast tego użyj innego unikalnego klucza nadrzędnego. b) jeśli identyfikatorami są identyfikatory GUID, można używać ich jako kluczy obcych. W tej sugestii zobaczysz mądrość podczas eksportowania i importowania rekordów lub kopiowania rekordów do innej bazy danych. Zbyt kłopotliwe jest zajmowanie się automatycznie generowanymi numerami sekwencji podczas migracji danych, gdy są one określane jako klucze obce.