SQL Server cannot truncate table

SQL Server no deja de sorprenderme, ahora me doy cuenta que no puedes truncar una tabla que tenga claves externas a otras tablas.

El error es el siguiente: Cannot truncate table ‘tablename' because it is being referenced by a FOREIGN KEY constraint.

¡Asombroso! y lo peor aún son las soluciones alternativas que se dan en algunos foros como por ejemplo:

1. eliminar todas las claves externas
2. truncar la tabla
3. volver a crear todas las claves externa

Eso a mi entender es trabajo de chinos (sin ofender), vale para una tabla con una clave externa, pero eso para tablas con unas cuantas claves realmente no es una solución.

La solución más atractiva que he encontrado es la siguiente:

1. borrar todos los registros de la tabla con delete *
2. reiniciar el índice

¿Cómo reiniciar el índice o clave principal de una tabla en SQL server?

Utilizaremos para ello la siguiente sintaxis DBCC CHECKIDENT (tabla, RESEED, 0)

Hay que tener en cuenta que el siguiente valor del índice será el valor puesto en la sintaxis + 1, por lo que, en este caso que he puesto 0 el siguiente dato que se inserte tendrá como índice el valor 1. Otra cosa a destacar es que puede que tengamos que incluir el nombre de la tabla entre comillas simples o entre corchetes si se hace referencia por una ruta de acceso completa, o si el nombre de la tabla tiene espacios en él (que en realidad no debería).

Si todo es correcto recibiremos el siguiente mensaje en SQL Manager:

Checking identity information: current identity value 'X', current column value '0'.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

Para comprobar que ha salido bien podemos utilizar DBCC CHECKIDENT (‘tablename’, NORESEED) y al ejecutarlo recibiremos el siguiente mensaje Checking identity information: current identity value '0', current column value 'X'.

Importante: El número de columnas NO se resetea con esta función, pero en realidad esto no lo utilizamos para nada.

¿Por qué no se puede utilizar truncate table en SQL server con foreign keys?

Porque TRUNCATE TABLE es un comando DDL y no puede comprobar si los registros de la tabla hacen referencia a un registro en la tabla secundaria. Esta es la razón por la que DELETE sin funciona y TRUNCATE TABLE no, porque con DELETE la base de datos es capaz de asegurarse de que no se hace referencia a otro registro.

Según la Wikipedia y cito:

Un lenguaje de definición de datos (Data Definition Language, DDL por sus siglas en inglés) es un lenguaje proporcionado por el sistema de gestión de base de datos que permite a los usuarios de la misma llevar a cabo las tareas de definición de las estructuras que almacenarán los datos así como de los procedimientos o funciones que permitan consultarlos.

Podemos ver el resto de instrucciones del lenguaje de definición de datos que soporta SQL server en el enlace anterior.