MS SQL Server: TRUNCATE TABLE vs FOREIGN KEY

Trafiłem ostatnio na ciekawy problem. Chciałem usunąć dane z kilkunastu powiązanych tabel. Chciałem je wyczyścić doszczętnie, więc uznałem, że TRUNCATE TABLE będzie zacną opcją i pierwsza mi się nasunęła. Rzeczywistość jednak szybko zweryfikowała mój pogląd na tę sytuację następującym komunikatem:

Cannot truncate table 'TableName' because it is being referenced by a FOREIGN KEY constraint.

Pierwszą moją myślą było to, że w złej kolejności czyszczę tabele, w co jednak jakoś nie wierzyłem - DELETE na tych tabelach z konkretnymi warunkami wykonywany w tej samej kolejności nie dawał takiego efektu. Oczywiście takie czyszczenie przez DELETE byłoby łatwiejsze, gdyby na kluczach obcych ustawiona była opcja ON DELETE CASCADE, ale ich modyfikacja nie wchodziła w grę. Sprawdziłem więc klucze obce - nie znalazłem nic interesującego, co by zostało przeoczone w kwestii kolejności tabel przy czyszczeniu. Testowo poprzesuwałem niektóre z nich i jedynie przy ponownym uruchomieniu skryptu błąd odnosił się do innej tabeli, kiedy tę problematyczną wrzuciłem gdzieś na sam koniec. Pojawiło się jednak coś z wyższym priorytetem i próbę zrozumienia tego musiałem zostawić na później.

W efekcie ktoś inny to przejął i był równie zdziwiony, co ja. Po kilku testach sprawdził coś, o czym też myślałem, ale już nie zdążyłem zrobić - dla tej konkretnej tabeli zamienił TRUNCATE na DELETE. Przeszło - jednak tym samym poszło do kolejnych linii, gdzie pojawił się analogiczny problem. Ponownie byliśmy zaskoczeni - nie spodziewałem się, że DELETE zignoruje klucz obcy. Zaciekawiło nas dodatkowo, że użycie TRUNCATE nawet na pustej tabeli zwróciło ten sam błąd, co wcześniej. Jak się okazało, trochę źle zinterpretowałem komunikat błędu. Zgodnie z tym, co jest napisane na TechNet:

You cannot use TRUNCATE TABLE on tables that:

• Are referenced by a FOREIGN KEY constraint. (You can truncate a table that has a foreign key that references itself.)
  
• Participate in an indexed view.
  
• Are published by using transactional replication or merge replication.
  

For tables with one or more of these characteristics, use the DELETE statement instead.

Pamiętałem, że dla DELETE ma znaczenie, czy istnieją konkretne dane, dla których mogłoby dojść do naruszenia constraintów i tak rozumiałem początkowo tę sytuację. Wyszło jednak na to, że TRUNCATE nie obchodzi co jest w tabeli, a jedynie sam fakt, czy jest jakiś constraint. Jeśli wziąć pod uwagę, że DELETE należy do DML, a TRUNCATE do DDL, w związku z czym "nie patrzy" na dane, brzmi to sensownie.

Alternatywną drogą, jaką rozważyłem było wyłączenie wszystkich constraintów, puszczenie TRUNCATE tak, jak chciałem to pierwotnie zrobić, po czym ponowne ich włączenie. Na StackOverflow można znaleźć prosty sposób, by to zrobić:

Okazuje się jednak, że o ile wystarczy to dla robienia UPDATE i INSERT bez dbania o constrainty, o tyle dla TRUNCATE to nie wystarczy i konieczne jest zdropowanie wszystkich constrantów i odtworzenie ich na nowo. Ponownie się zdziwiłem. Ciekawe, czy coś jeszcze mnie zaskoczy w tym temacie. Dostałem w efekcie cynk o procedurze, którą napisał Karl Kroneck do usuwania i odtwarzania constraintów i robienia TRUNCATE. Więcej info i sam kod procedury dostępny jest tutaj.

Przy okazji znalazłem na SO listę różnic między DELETE i TRUNCATE, można się czegoś dowiedzieć ciekawego.