Error 547 – naruszenie klucza obcego

Error 547 sygnalizuje naruszenie ograniczenia klucza obcego podczas próby modyfikacji lub usunięcia powiązanych danych.

Co oznacza komunikat SQL Server Error 547

SQL Server Error 547 oznacza, że wykonana instrukcja naruszyła ograniczenie integralności referencyjnej, najczęściej zdefiniowane przez klucz obcy (FOREIGN KEY). Klucz obcy wiąże kolumnę lub zestaw kolumn w tabeli podrzędnej z kluczem podstawowym albo innym unikatowym kluczem w tabeli nadrzędnej. Dzięki temu SQL Server może wymagać, aby każda wartość referencyjna wskazywała istniejący rekord. Błąd pojawia się wtedy, gdy operacja INSERT lub UPDATE próbuje zapisać w tabeli podrzędnej wartość, dla której nie ma odpowiadającego wiersza w tabeli nadrzędnej.

Ten sam numer błędu może wystąpić podczas usuwania lub zmiany rekordu nadrzędnego. Jeżeli w tabeli podrzędnej istnieją wiersze odwołujące się do danego rekordu, instrukcja DELETE może zostać odrzucona, ponieważ pozostawiłaby osierocone odwołania. Analogicznie zmiana wartości klucza w tabeli nadrzędnej może naruszyć istniejące relacje. Treść komunikatu zwykle wskazuje nazwę ograniczenia, tabelę i kolumnę, które uczestniczą w konflikcie, dlatego sam numer 547 nie wystarcza do ustalenia konkretnej przyczyny.

Jak SQL Server wykrywa naruszenie relacji

Podczas wykonywania modyfikacji SQL Server sprawdza zgodność wartości z definicją ograniczenia FOREIGN KEY. Przy dodawaniu lub aktualizowaniu wiersza w tabeli podrzędnej wyszukiwany jest odpowiadający klucz w tabeli nadrzędnej. Jeżeli rekord nadrzędny nie istnieje, transakcja otrzymuje błąd 547. Przy usuwaniu lub aktualizowaniu rekordu nadrzędnego silnik sprawdza natomiast, czy inne wiersze nadal wskazują na zmienianą wartość. Sprawdzenie dotyczy rzeczywistego stanu danych w chwili wykonywania instrukcji, a nie tylko wartości widocznych wcześniej w aplikacji.

Znaczenie ma również kolejność operacji w transakcji. Próba utworzenia rekordu podrzędnego przed zapisaniem wymaganego rekordu nadrzędnego może zakończyć się błędem, nawet jeśli oba zapisy są częścią tej samej transakcji. W typowym przepływie najpierw dodaje się rekord nadrzędny, następnie pobiera jego identyfikator, a dopiero później zapisuje rekordy zależne. Przy usuwaniu kolejność jest zwykle odwrotna, chyba że ograniczenie zostało skonfigurowane z akcją kaskadową. Błąd może też ujawnić rozbieżność między identyfikatorem przechowywanym w formularzu lub pamięci aplikacji a identyfikatorem faktycznie istniejącym w bazie.

Rozpoznawanie przyczyny w instrukcji INSERT, UPDATE i DELETE

Pierwszym krokiem jest odczytanie pełnego komunikatu, w szczególności nazwy ograniczenia, tabeli i kolumny. Następnie należy sprawdzić definicję klucza obcego w schemacie SQL Server oraz porównać wartości uczestniczące w operacji. Dla błędu przy zapisie rekordu podrzędnego trzeba ustalić, czy wskazywany klucz nadrzędny istnieje, czy nie jest pusty w sposób niezgodny z regułami aplikacji oraz czy typy i wartości kolumn są przekazywane bez niezamierzonej konwersji. Przy błędzie podczas usuwania warto wyszukać rekordy podrzędne odwołujące się do identyfikatora przeznaczonego do usunięcia.

Ważne jest odróżnienie błędu danych od błędu logiki aplikacji. Jeżeli użytkownik wybiera istniejący element, ale aplikacja wysyła inny identyfikator, problem może znajdować się w mapowaniu formularza, filtrowaniu listy, obsłudze wartości domyślnej albo w kodzie aktualizującym stan obiektu. Jeżeli rekord nadrzędny został usunięty wcześniej przez inną operację, przyczyną może być wyścig między równoległymi żądaniami. Z kolei częściowo wykonany import może próbować zapisywać dane zależne bez zachowania wymaganej kolejności. Analiza powinna obejmować parametry rzeczywiście przekazane do SQL Server, granice transakcji oraz obsługę wyjątków, a nie tylko zapytanie widoczne w kodzie.

Bezpieczne sposoby usunięcia Error 547

Naprawa zależy od zamierzonej reguły biznesowej. Gdy rekord podrzędny wskazuje błędny albo nieistniejący identyfikator, należy skorygować dane wejściowe lub najpierw utworzyć właściwy rekord nadrzędny. Gdy usuwany rekord ma zależności, można zgodnie z wymaganiami procesu usunąć lub przepiąć rekordy podrzędne, a następnie ponowić usunięcie nadrzędnego. Nie należy wyłączać ograniczenia tylko po to, aby wymusić wykonanie operacji, ponieważ może to doprowadzić do powstania niespójnych danych.

Akcje ON DELETE CASCADE i ON UPDATE CASCADE powinny być stosowane wyłącznie wtedy, gdy automatyczne propagowanie zmiany jest rzeczywiście pożądane. Kaskadowe usuwanie może objąć wiele tabel i rekordów, dlatego przed zmianą definicji ograniczenia trzeba przeanalizować cały łańcuch zależności. W aplikacji warto walidować istnienie klucza nadrzędnego przed zapisem, wykonywać powiązane operacje w kontrolowanej transakcji oraz rejestrować nazwę ograniczenia i parametry nieudanej operacji. Diagnostyka SQL Server Error 547 na komputerze wymaga więc sprawdzenia zarówno schematu bazy, jak i programu, który przekazuje dane do silnika.