To jest sedno ograniczeń klucza obcego: powstrzymują cię przed usuwaniem danych, o których mowa w innym miejscu w celu zachowania integralności referencyjnej.
Istnieją dwie opcje:
- Usuń wiersze od
INVENTORY_ITEMSpierwszego, a następnie wiersze od STOCK_ARTICLES.
- Użyj
ON DELETE CASCADEdla definicji klucza.
1: Usuwanie we właściwej kolejności
Najbardziej efektywny sposób wykonania tego zadania różni się w zależności od złożoności zapytania, które decyduje o tym, które wiersze należy usunąć. Ogólny wzór może być:
BEGIN TRANSACTION
SET XACT_ABORT ON
DELETE INVENTORY_ITEMS WHERE STOCK_ARTICLE IN (<select statement that returns stock_article.id for the rows you are about to delete>)
DELETE STOCK_ARTICLES WHERE <the rest of your current delete statement>
COMMIT TRANSACTION
Jest to przydatne w przypadku prostych zapytań lub do usunięcia pojedynczego elementu zapasowego, ale biorąc pod uwagę, że instrukcja delete zawiera WHERE NOT EXISTSzagnieżdżoną klauzulę, która WHERE INmoże stworzyć bardzo nieefektywny plan, więc przetestuj z realistycznym rozmiarem zestawu danych i w razie potrzeby zmień kolejność zapytania.
Zwróć także uwagę na wyciągi z transakcji: chcesz się upewnić, że oba usunięcia zostały zakończone, lub żadne z nich nie wykona. Jeśli operacja już się dzieje w ramach transakcji, oczywiście musisz to zmienić, aby dopasować ją do bieżącej transakcji i procesu obsługi błędów.
2: Użyj ON DELETE CASCADE
Jeśli dodasz opcję kaskadową do klucza obcego, SQL Server zrobi to automatycznie, usuwając wiersze, INVENTORY_ITEMSaby spełnić ograniczenie, że nic nie powinno odnosić się do usuwanych wierszy. Po prostu dodaj ON DELETE CASCADEdo definicji FK tak:
ALTER TABLE <child_table> WITH CHECK
ADD CONSTRAINT <fk_name> FOREIGN KEY(<column(s)>)
REFERENCES <parent_table> (<column(s)>)
ON DELETE CASCADE
Zaletą tego rozwiązania jest to, że usunięcie jest jedną instrukcją atomową, która zmniejsza (choć jak zwykle nie usuwa w 100%) potrzebę martwienia się o ustawienia transakcji i blokady. Kaskada może nawet działać na wielu poziomach rodzic / dziecko / wnuczek / ..., jeśli istnieje tylko jedna ścieżka między rodzicem a wszystkimi potomkami (wyszukaj „wiele ścieżek kaskady”, aby zobaczyć przykłady, w których może to nie działać).
UWAGA: Ja i wielu innych uważam, że usuwanie kaskadowe jest niebezpieczne, więc jeśli użyjesz tej opcji, bardzo ostrożnie udokumentuj ją w projekcie bazy danych, abyś Ty i inni programiści nie potknęli się o to później . Z tego powodu unikam usuwania kaskadowego w miarę możliwości.
Częstym problemem powodowanym przez kaskadowe usuwanie jest sytuacja, gdy ktoś aktualizuje dane, upuszczając i odtwarzając wiersze zamiast przy użyciu UPDATElub MERGE. Jest to często widoczne, gdy „zaktualizuj wiersze, które już istnieją, wstaw te, które nie” (czasami nazywane operacją UPSERT), a osobom nieświadomym MERGEinstrukcji łatwiej jest to zrobić:
DELETE <all rows that match IDs in the new data>
INSERT <all rows from the new data>
niż
-- updates
UPDATE target
SET <col1> = source.<col1>
, <col2> = source.<col2>
...
, <colN> = source.<colN>
FROM <target_table> AS target JOIN <source_table_or_view_or_statement> AS source ON source.ID = target.ID
-- inserts
INSERT <target_table>
SELECT *
FROM <source_table_or_other> AS source
LEFT OUTER JOIN
<target_table> AS target
ON target.ID = source.ID
WHERE target.ID IS NULL
Problem polega na tym, że instrukcja delete będzie kaskadowana do wierszy podrzędnych, a instrukcja insert nie odtworzy ich, więc podczas aktualizacji tabeli nadrzędnej przypadkowo tracisz dane z tabel podrzędnych.
Podsumowanie
Tak, najpierw musisz usunąć wiersze potomne.
Nie ma innej opcji: ON DELETE CASCADE.
Ale ON DELETE CASCADEmoże być niebezpieczny , więc używaj go ostrożnie.
Uwaga dodatkowa: używaj MERGE( UPDATElub - i INSERTgdzie MERGEnie jest dostępne), gdy potrzebujesz UPSERToperacji, a nie DELETE - a następnie - zamień - z, INSERTaby uniknąć wpadnięcia w pułapki ustanowione przez inne osoby ON DELETE CASCADE.
INVENTORY_ITEMSdodawane między dwiemaDELETEs.