547 – Naruszenie ograniczenia klucza obcego

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

Co oznacza błąd 547 w SQL Server

Błąd 547 w Microsoft SQL Server pojawia się wtedy, gdy wykonywana instrukcja narusza ograniczenie zdefiniowane w bazie danych. W kontekście klucza obcego oznacza to próbę zapisania, zmodyfikowania albo usunięcia danych w sposób, który pozostaje sprzeczny z relacją między tabelą nadrzędną a tabelą podrzędną. Klucz obcy wskazuje kolumnę lub zestaw kolumn, których wartości muszą odpowiadać istniejącym wartościom klucza głównego albo innego klucza unikatowego w tabeli nadrzędnej.

Najczęściej komunikat występuje przy usuwaniu rekordu nadrzędnego, do którego odwołują się rekordy podrzędne. Przykładem jest usunięcie klienta, gdy w tabeli zamówień nadal znajdują się zamówienia powiązane z jego identyfikatorem. Podobny problem może wystąpić podczas aktualizacji wartości klucza nadrzędnego albo podczas wstawiania rekordu podrzędnego z wartością, której nie ma w tabeli nadrzędnej. SQL Server przerywa wtedy instrukcję, aby nie dopuścić do powstania niespójnego powiązania.

Relacja nadrzędna i podrzędna podczas operacji DML

Ograniczenie klucza obcego jest sprawdzane podczas operacji DML, czyli INSERT, UPDATE i DELETE. Przy wstawianiu wiersza do tabeli podrzędnej SQL Server sprawdza, czy wartość klucza obcego istnieje w tabeli nadrzędnej. Przy zmianie tego pola sprawdzana jest nowa wartość. Przy usuwaniu wiersza nadrzędnego silnik analizuje, czy inne wiersze nadal się do niego odwołują.

Sposób reakcji na usunięcie lub zmianę rekordu nadrzędnego zależy od konfiguracji ograniczenia. Możliwe jest pozostawienie domyślnej blokady operacji, automatyczne usunięcie rekordów podrzędnych przez ON DELETE CASCADE albo ustawienie wartości klucza obcego na NULL przez ON DELETE SET NULL, jeżeli kolumna dopuszcza wartości puste. Analogiczne reguły mogą dotyczyć aktualizacji klucza nadrzędnego. Nie należy jednak dodawać kaskady wyłącznie po to, aby ukryć błąd 547, ponieważ może ona usunąć większy zestaw danych niż zamierzono.

Przy ocenie problemu trzeba rozróżnić błąd relacji od zwykłego braku danych. Jeżeli aplikacja tworzy rekord podrzędny przed zapisaniem odpowiadającego mu rekordu nadrzędnego, kolejność operacji jest nieprawidłowa. Jeżeli natomiast rekord nadrzędny został usunięty wcześniej, aplikacja może korzystać z nieaktualnego identyfikatora. W obu przypadkach sam komunikat 547 jest skutkiem naruszenia reguły integralności, a nie przyczyną błędnych danych wejściowych.

Ustalenie ograniczenia powodującego naruszenie

Komunikat SQL Server zwykle zawiera nazwę ograniczenia, nazwę tabeli i kolumny, które uczestniczą w konflikcie. Te informacje należy odczytać z pełnej treści błędu, a nie tylko z numeru 547. Nazwę ograniczenia można następnie wyszukać w metadanych bazy. Przykładowe zapytanie pozwalające sprawdzić klucze obce ma postać:

SELECT fk.name AS foreign_key_name, OBJECT_SCHEMA_NAME(fk.parent_object_id) AS child_schema, OBJECT_NAME(fk.parent_object_id) AS child_table, OBJECT_SCHEMA_NAME(fk.referenced_object_id) AS parent_schema, OBJECT_NAME(fk.referenced_object_id) AS parent_table, fk.delete_referential_action_desc, fk.update_referential_action_desc FROM sys.foreign_keys AS fk WHERE fk.name = N'NAZWA_OGRANICZENIA';

Jeżeli nazwa ograniczenia nie jest znana, można sprawdzić wszystkie kolumny uczestniczące w relacjach, korzystając z widoków sys.foreign_keys, sys.foreign_key_columns, sys.tables i sys.columns. Następnie należy porównać konkretną wartość klucza obcego z rekordami tabeli nadrzędnej. W przypadku usuwania warto sprawdzić, które rekordy podrzędne blokują operację; w przypadku wstawiania lub aktualizacji trzeba potwierdzić, czy nowa wartość ma odpowiadający rekord nadrzędny.

Bezpieczne usunięcie lub poprawienie konfliktu

Naprawa zależy od zamierzonego modelu danych. Jeśli rekord nadrzędny ma pozostać w bazie, należy wycofać jego usunięcie i poprawić procedurę albo aplikację. Jeżeli rekord podrzędny jest niepotrzebny, można usunąć go przed rekordem nadrzędnym, ale kolejność i zakres zmian powinny wynikać z reguł biznesowych. Gdy rekord podrzędny ma zachować historię, właściwsze może być pozostawienie rekordu nadrzędnego, ustawienie relacji na wartość dopuszczoną przez model albo zastosowanie kontrolowanej zmiany statusu zamiast fizycznego usunięcia.

Operacje obejmujące wiele powiązanych tabel powinny być wykonywane w transakcji. Pozwala to zatwierdzić kompletny zestaw zmian albo wycofać go w całości, jeśli kolejny etap zakończy się błędem. Przed zmianą ograniczenia należy zweryfikować istniejące dane, typy kolumn, możliwość użycia wartości NULL oraz regułę, która ma obowiązywać po zmianie. Wyłączanie klucza obcego lub używanie WITH NOCHECK może pozostawić niesprawdzone i niespójne rekordy, dlatego nie jest właściwą metodą usuwania przyczyny błędu 547.

Jeżeli błąd pojawia się na komputerze podczas pracy programu korzystającego z lokalnej bazy SQL Server, przydatne jest zapisanie pełnego komunikatu, nazwy wykonywanej operacji i momentu wystąpienia problemu. Takie dane ułatwiają odtworzenie kolejności zapisu oraz odróżnienie problemu w aplikacji od uszkodzenia lub niezgodności lokalnych danych. Warszawskie Pogotowie Komputerowe wykonuje profesjonalną naprawę laptopów, komputerów PC i ich podzespołów, w tym diagnostykę problemów z oprogramowaniem uruchamianym na komputerze.