Nie wahaj się nałożyć ograniczeń na bazę danych. Na pewno będziesz mieć spójną bazę danych, a to jeden z dobrych powodów, aby korzystać z bazy danych. Zwłaszcza jeśli masz kilka aplikacji, które o to proszą (lub tylko jedną aplikację, ale z trybem bezpośrednim i trybem wsadowym używającym różnych źródeł).
Z MySQL nie masz zaawansowanych ograniczeń, tak jak w PostgreSQL, ale przynajmniej ograniczenia klucza obcego są dość zaawansowane.
Weźmy przykład tabeli firmowej ze tabelą użytkowników 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 :
- PRZY OGRANICZENIU AKTUALIZACJI : wartość domyślna : jeśli spróbujesz zaktualizować identyfikator_firmy w tabeli FIRMA, silnik odrzuci operację, jeśli przynajmniej jeden UŻYTKOWNIK przynajmniej wyświetli linki do tej firmy.
- PRZY AKTUALIZACJI BRAK DZIAŁANIA : tak samo jak OGRANICZENIE.
- PRZY AKTUALIZACJI KASKADU : zwykle najlepszy : jeśli zaktualizujesz identyfikator_firmy w wierszu tabeli FIRMA, silnik odpowiednio go zaktualizuje we wszystkich wierszach UŻYTKOWNIKA odnoszących się do tej FIRMY (ale nie aktywowano żadnych wyzwalaczy w tabeli USER, ostrzeżenie). Silnik będzie śledził zmiany dla Ciebie, jest dobry.
- ON UPDATE SET NULL : jeśli zaktualizujesz identyfikator_firmy w wierszu tabeli FIRMA, silnik ustawi powiązanych UŻYTKOWNIKÓW identyfikator_firmy na NULL (powinien być dostępny w polu USER nazwa_firmy). W aktualizacji nie widzę nic interesującego, ale mogę się mylić.
A teraz po stronie ON DELETE :
- PRZY USUNIĘCIU OGRANICZENIA : wartość domyślna : jeśli spróbujesz usunąć identyfikator id_firmy w tabeli FIRMA, silnik odrzuci operację, jeśli przynajmniej jeden UŻYTKOWNIK przynajmniej linki do tej firmy, może uratować ci życie.
- PRZY USUNIĘCIU BRAK DZIAŁANIA : to samo co OGRANICZENIE
- PRZY USUNIĘCIU KASKADY : niebezpieczne : jeśli usuniesz wiersz firmy w tabeli FIRMA, silnik usunie również powiązanych UŻYTKOWNIKÓW. Jest to niebezpieczne, ale można go użyć do automatycznego czyszczenia tabel pomocniczych (więc może to być coś, czego chcesz, ale na pewno nie dla przykładu FIRMA <-> UŻYTKOWNIKA)
- USUŃ USUŃ NULL : garść : jeśli usuniesz wiersz FIRMY, powiązani UŻYTKOWNICY automatycznie będą mieli związek z NULL. Jeśli wartość Null to wartość dla użytkowników bez firmy, może to być dobre zachowanie, na przykład może trzeba zatrzymać użytkowników w aplikacji, jako autorów niektórych treści, ale usunięcie firmy nie stanowi dla ciebie problemu.
zazwyczaj moją domyślną wartością jest: PRZY USUNIĘCIU OGRANICZENIA PRZY AKTUALIZACJI KASKADY . z niektórymi ON DELETE CASCADEdla tabel śledzenia (dzienniki - nie wszystkie dzienniki - itp.), a ON DELETE SET NULLgdy tabela główna jest „prostym atrybutem” dla tabeli zawierającej klucz obcy, jak tabela JOB dla tabeli USER.
Edytować
Minęło dużo czasu, odkąd to napisałem. Teraz myślę, że powinienem dodać jedno ważne ostrzeżenie. MySQL ma jedno duże udokumentowane ograniczenie z kaskadami. Kaskady nie uruchamiają wyzwalaczy . Jeśli więc masz wystarczającą pewność siebie w tym silniku, aby używać wyzwalaczy, powinieneś unikać ograniczeń kaskadowych.
Wyzwalacze MySQL aktywują się tylko dla zmian wprowadzonych do tabel przez instrukcje SQL. Nie aktywują się w przypadku zmian w widokach ani zmian w tabelach dokonanych przez interfejsy API, które nie przesyłają instrukcji SQL do serwera MySQL
==> Zobacz poniżej ostatniej edycji, sprawy idą w tej domenie
Wyzwalacze nie są aktywowane przez działania na kluczu obcym.
I nie sądzę, że zostanie to kiedyś naprawione. Ograniczeniami klucza obcego zarządza pamięć InnoDb, a wyzwalacze są zarządzane przez silnik SQL MySQL. Oba są rozdzielone. Innodb to jedyna pamięć masowa z zarządzaniem ograniczeniami, może kiedyś dodadzą wyzwalacze bezpośrednio w silniku pamięci, a może nie.
Ale mam własne zdanie na temat tego, który element należy wybrać między słabą implementacją wyzwalacza a bardzo przydatną obsługą ograniczeń kluczy obcych. A kiedy już przyzwyczaisz się do spójności bazy danych, pokochasz PostgreSQL.
12/2017 - Aktualizacja tej edycji o MySQL:
jak stwierdził @IstiaqueAhmed w komentarzach, sytuacja uległa zmianie w tym temacie. Kliknij link i sprawdź rzeczywistą aktualną sytuację (która może ulec zmianie w przyszłości).
ON DELETE CASCADE : dangerous- weź ze szczyptą soli.