SQL Server cannot truncate table

SQL Server 2008 wallpaper 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.

Comentarios

PabloDoval ha dicho que…
Reynier, gracias por la entrada! De todos modos, me gustaría aclarar una cosa al respecto de tu entrada: la opcion que planteas del DELETE y resetear la semilla del identity no es sinonimo de un TRUNCATE.

Para algunos escenarios te servirá, pero no olvides que el TRUNCATE es una operacion 'no logeada', esto es, no registra cada modificacion (en este caso, borrados) en el log de transacciones, mientras que el DELETE si lo hace.

Para que te hagas a la idea, en el entorno de desarrollo del DataMart en el que estoy actualmente, con multiples tablas de mas de 50 millones de registros, un borrado con DELETE, bien optimizado en batches para evitar crecimientos del log de transacciones, tarda unas 3 horas. El truncado de estas tablas, sin embargo, no llega a 10 segundos.

Por tanto, en ocasiones si que merece la pena tomarse la molestia de hacer todas las eliminaciones de ls FK, despues el truncado y despues la reconstruccion.

Por ultimo, comentarte que no es del todo cierto que la razon por la que el truncate table no se pueda usar con FKs es que sea un comando DDL; la razon es que es una operacion no logeada, y no puede propagar cambios a objetos que si puedan estar sometidos a logeo. Si lo permitiera, un truncado de una tabla podria afectar a la recuperabilidad de operaciones en otras tablas. Es un problema interesante :)

Dices que SQL Server no deja de asombrarte.. eso es genial! A mi tampoco, el dia que la informatica no nos asombre, mejor cambiamos de profesion ¿no? :)
Muchas gracias Pablo por tu comentario.

La verdad que muchos de los aspectos tan profundos de SQL Server se me escapan de ahí que agradezca mucho esas aclaraciones y teniendo en cuenta lo que me dices no dejas de tener razón.

Un gran saludo y gracias nuevamente