Ograniczenia klucza obcego: kiedy używać przy aktualizacji i usuwaniu

Nie wahaj się umieścić ograniczenia w bazie danych. Będziesz mieć pewność, że masz spójną bazę danych, i to jest jeden z dobrych powodów, aby korzystać z bazy danych. Zwłaszcza jeśli masz kilka aplikacji żądających go (lub tylko jedną aplikację, ale z trybem bezpośrednim i trybem wsadowym przy użyciu różnych źródeł).

W MySQL nie masz zaawansowanych ograniczeń jak w postgreSQL, ale przynajmniej ograniczenia klucza obcego są dość zaawansowane.

We ' ll take an example, a tabela firmowa z tabelą użytkownika zawierającą osoby z tej firmy

CREATE TABLE COMPANY (
     company_id INT NOT NULL,
     company_name VARCHAR(50),
     PRIMARY KEY (company_id)
) ENGINE=INNODB;

CREATE TABLE USER (
     user_id INT, 
     user_name VARCHAR(50), 
     company_id INT,
     INDEX company_id_idx (company_id),
     FOREIGN KEY (company_id) REFERENCES COMPANY (company_id) ON...
) ENGINE=INNODB;

Spójrzmy na klauzulę on UPDATE :

• ON UPDATE : domyślna: jeśli spróbujesz zaktualizować company_id w tabeli COMPANY, silnik odrzuci operację, jeśli jeden użytkownik połączy się przynajmniej z tą firmą.
• ON UPDATE NO ACTION : to samo co RESTRICT.
• on update CASCADE : najlepszy zazwyczaj : Jeśli zaktualizuj company_id w wierszu table COMPANY silnik zaktualizuje go odpowiednio we wszystkich wierszach użytkownika odnoszących się do tej firmy (ale nie aktywuje wyzwalaczy w tabeli USER, Ostrzeżenie). Silnik będzie śledzić zmiany za Ciebie, to jest dobre.
• przy UPDATE SET NULL : jeśli zaktualizujesz company_id w wierszu tabeli COMPANY_ID, silnik ustawi powiązanych użytkowników company_id NA NULL(powinien być dostępny w polu User company_id). Nie widzę nic ciekawego do zrobienia z tym na update, ale mogę się mylić.

A teraz na na DELETE stronie:

• on DELETE : domyślnie: jeśli spróbujesz usunąć identyfikator company_id w tabeli COMPANY, silnik odrzuci operację, jeśli jeden użytkownik co najmniej łączy się z tą firmą, może uratować Ci życie.
• on DELETE NO ACTION : to samo co RESTRICT
• ON DELETE CASCADE : dangerous : Jeśli usuniesz wiersz company w tabeli COMPANY the silnik usunie również powiązanych użytkowników. Jest to niebezpieczne, ale może być używane do automatycznego czyszczenia tabel drugorzędnych (więc może to być coś, czego chcesz, ale na pewno nie dla przykładu użytkownika firmy) {]}
• ON DELETE SET NULL : garść: Jeśli usuniesz wiersz firmowy, powiązani użytkownicy automatycznie będą mieli relację do NULL. Jeśli Null jest twoją wartością dla użytkowników bez firmy, może to być dobre zachowanie, na przykład może musisz zachować użytkowników w Twojej aplikacji, jako autorów niektórych treści, ale usunięcie firmy nie jest dla Ciebie problemem.

Zwykle moim domyślnym jest: na DELETE RESTRICT na UPDATE CASCADE. z niektórymi {[1] } dla tabel ścieżek (logs--not all logs--, things like that) I ON DELETE SET NULL, gdy tabela główna jest 'prostym atrybutem' dla tabeli zawierającej klucz obcy, jak tabela zadań dla tabeli USER.

Edit

• http://dev.mysql.com/doc/refman/5.6/en/triggers.html

Wyzwalacze MySQL aktywują tylko dla zmian dokonanych w tabelach za pomocą poleceń SQL. Nie aktywują się dla zmian w widokach, ani przez zmiany do tabele tworzone przez API, które nie przesyłają poleceń SQL do serwera MySQL

• http://dev.mysql.com/doc/refman/5.6/en/stored-program-restrictions.html#stored-routines-trigger-restrictions

==> Zobacz poniżej ostatnią edycję, rzeczy poruszają się na tej domenie

Wyzwalacze nie są aktywowane przez akcje klucza obcego.

Ale mam własne zdanie na temat tego, który element należy wybrać pomiędzy słabą implementacją trigger a bardzo przydatną obsługą ograniczeń kluczy obcych. A gdy przyzwyczaisz się do spójności bazy danych, pokochasz PostgreSQL.

12/2017-aktualizacja tej edycji informacje o MySQL:

Jak stwierdził @IstiaqueAhmed w komentarzach, sytuacja zmieniła się w tym temacie. Więc kliknij link i sprawdź rzeczywistą aktualną sytuację (która może się ponownie zmienić w przyszłości).