sobota, 18 stycznia 2014

Klucze obce

Czy zakadając klucze obce w tabelach zastanawiacie się nad ich ilością? Tzn ile razy dana tabela jest powiązana z innymi tabelami? Według BOL SQL Server nie posiada predefiniowanego limitu kluczy obcych w jednej tabeli, jak też ilości referencji do niej z innych tabel. Jednakże MS zaleca aby liczba ta w jednym jak i drugim przypadku nie była większa niż 253.

Co się może wydarzyć gdy powiązań tych jest więcej?

Otóż proste zapytanie
DELETE FROM MyTable WHERE ID = @ID
Może skutkować następującym błędem

The query processor ran out of stack space during query optimization. Please simplify the query.
 
Co ciekawe ten błąd jest uzależniony od konfiguracji sprzętowej. Na jednym serwerze zapytanie może się wykonać poprawnie a na innym już nie. I nie ma tutaj żadnego znaczenia fakt, że w tabelach które powiązane są z MyTable kluczem obcym nie ma żadnego rekordu.

Co więc zrobić, gdy trzeba usunąć rekord a SQL Server nie może sobie z tym poradzić? Rozwiązaniem może być opakowanie skryptu, który najpier wyłączy klucze obce, usunie to co trzeba usunąć ze wszystkich tabel zależnych i właściwej tabeli a później ponownie włączy klucze obce, w transakcję. Tak by w przypadku niepowodzenia powrócić do stanu poprzedniego i próbować w razie czego inaczej zabierać się za rozwiązanie problemu.

Pamiętajcie, że klucze obce mają wpływ na wydajność zapytań modyfikujących dane. Trochę więcej szczegółów i przykład planu wykonania znajdziecie tutaj.