¿No se puede truncar la tabla porque está siendo referenciada por una restricción FOREIGN KEY?

Usando MSSQL2005, ¿puedo truncar una tabla con una restricción de clave externa si trunco ​​primero la tabla hija (la tabla con la clave primaria de la relación FK)?

Sé que puedo cualquiera

  • Use un DELETE sin una cláusula where y luego RESEED la identidad (o)
  • Elimine FK, trunque la tabla y vuelva a crear el FK.

Pensé que siempre que truncara la tabla hija antes que el padre, estaría bien sin hacer ninguna de las opciones anteriores, pero estoy obteniendo este error:

No se puede truncar la tabla ‘TableName’ porque está siendo referenciada por una restricción FOREIGN KEY.

Correcto; no puede truncar una tabla que tiene una restricción FK sobre ella.

Por lo general, mi proceso para esto es:

  1. Deja las restricciones
  2. Trunc la mesa
  3. Recrea las restricciones.

(Todo en una transacción, por supuesto)

Por supuesto, esto solo se aplica si el niño ya ha sido truncado. De lo contrario, voy por una ruta diferente, que depende completamente de cómo se ve mi información. (Demasiadas variables para entrar aquí.)

El póster original determinó POR QUÉ este es el caso; ver esta respuesta para más detalles.

 DELETE FROM TABLENAME DBCC CHECKIDENT ('DATABASENAME.dbo.TABLENAME',RESEED, 0) 

Tenga en cuenta que esto probablemente no sea lo que usted desearía si tiene millones de registros, ya que es muy lento.

Debido a que TRUNCATE TABLE es un comando DDL , no puede verificar si los registros de la tabla están siendo referenciados por un registro en la tabla secundaria.

Esta es la razón por la que DELETE funciona y TRUNCATE TABLE no: porque la base de datos puede asegurarse de que no esté siendo referenciada por otro registro.

Sin ALTER TABLE

 -- Delete all records DELETE FROM [TableName] -- Set current ID to "1" -- If table already contains data, use "0" -- If table is empty and never insert data, use "1" -- Use SP https://github.com/reduardo7/TableTruncate DBCC CHECKIDENT ([TableName], RESEED, [0|1]) 

Como procedimiento almacenado

https://github.com/reduardo7/TableTruncate

Tenga en cuenta que esto probablemente no sea lo que usted desearía si tiene millones de registros, ya que es muy lento.

La solución @denver_citizen proporcionada anteriormente no funcionó para mí, pero me gustó su espíritu, así que modifiqué algunas cosas:

  • lo convirtió en un procedimiento almacenado
  • cambió la forma en que las claves externas se rellenan y se vuelven a crear
  • la secuencia de comandos original trunca todas las tablas a las que se hace referencia, esto puede causar un error de violación de clave externa cuando la tabla a la que se hace referencia tiene otras referencias de clave externa. Este script trunca solo la tabla especificada como parámetro. Depende del usuario, llamar a este procedimiento almacenado varias veces en todas las tablas en el orden correcto

Para el beneficio del público, aquí está el guión actualizado:

 CREATE PROCEDURE [dbo].[truncate_non_empty_table] @TableToTruncate VARCHAR(64) AS BEGIN SET NOCOUNT ON -- GLOBAL VARIABLES DECLARE @i int DECLARE @Debug bit DECLARE @Recycle bit DECLARE @Verbose bit DECLARE @TableName varchar(80) DECLARE @ColumnName varchar(80) DECLARE @ReferencedTableName varchar(80) DECLARE @ReferencedColumnName varchar(80) DECLARE @ConstraintName varchar(250) DECLARE @CreateStatement varchar(max) DECLARE @DropStatement varchar(max) DECLARE @TruncateStatement varchar(max) DECLARE @CreateStatementTemp varchar(max) DECLARE @DropStatementTemp varchar(max) DECLARE @TruncateStatementTemp varchar(max) DECLARE @Statement varchar(max) -- 1 = Will not execute statements SET @Debug = 0 -- 0 = Will not create or truncate storage table -- 1 = Will create or truncate storage table SET @Recycle = 0 -- 1 = Will print a message on every step set @Verbose = 1 SET @i = 1 SET @CreateStatement = 'ALTER TABLE [dbo].[] WITH NOCHECK ADD CONSTRAINT [] FOREIGN KEY([]) REFERENCES [dbo].[] ([])' SET @DropStatement = 'ALTER TABLE [dbo].[] DROP CONSTRAINT []' SET @TruncateStatement = 'TRUNCATE TABLE []' -- Drop Temporary tables IF OBJECT_ID('tempdb..#FKs') IS NOT NULL DROP TABLE #FKs -- GET FKs SELECT ROW_NUMBER() OVER (ORDER BY OBJECT_NAME(parent_object_id), clm1.name) as ID, OBJECT_NAME(constraint_object_id) as ConstraintName, OBJECT_NAME(parent_object_id) as TableName, clm1.name as ColumnName, OBJECT_NAME(referenced_object_id) as ReferencedTableName, clm2.name as ReferencedColumnName INTO #FKs FROM sys.foreign_key_columns fk JOIN sys.columns clm1 ON fk.parent_column_id = clm1.column_id AND fk.parent_object_id = clm1.object_id JOIN sys.columns clm2 ON fk.referenced_column_id = clm2.column_id AND fk.referenced_object_id= clm2.object_id --WHERE OBJECT_NAME(parent_object_id) not in ('//tables that you do not wont to be truncated') WHERE OBJECT_NAME(referenced_object_id) = @TableToTruncate ORDER BY OBJECT_NAME(parent_object_id) -- Prepare Storage Table IF Not EXISTS(SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'Internal_FK_Definition_Storage') BEGIN IF @Verbose = 1 PRINT '1. Creating Process Specific Tables...' -- CREATE STORAGE TABLE IF IT DOES NOT EXISTS CREATE TABLE [Internal_FK_Definition_Storage] ( ID int not null identity(1,1) primary key, FK_Name varchar(250) not null, FK_CreationStatement varchar(max) not null, FK_DestructionStatement varchar(max) not null, Table_TruncationStatement varchar(max) not null ) END ELSE BEGIN IF @Recycle = 0 BEGIN IF @Verbose = 1 PRINT '1. Truncating Process Specific Tables...' -- TRUNCATE TABLE IF IT ALREADY EXISTS TRUNCATE TABLE [Internal_FK_Definition_Storage] END ELSE PRINT '1. Process specific table will be recycled from previous execution...' END IF @Recycle = 0 BEGIN IF @Verbose = 1 PRINT '2. Backing up Foreign Key Definitions...' -- Fetch and persist FKs WHILE (@i <= (SELECT MAX(ID) FROM #FKs)) BEGIN SET @ConstraintName = (SELECT ConstraintName FROM #FKs WHERE ID = @i) SET @TableName = (SELECT TableName FROM #FKs WHERE ID = @i) SET @ColumnName = (SELECT ColumnName FROM #FKs WHERE ID = @i) SET @ReferencedTableName = (SELECT ReferencedTableName FROM #FKs WHERE ID = @i) SET @ReferencedColumnName = (SELECT ReferencedColumnName FROM #FKs WHERE ID = @i) SET @DropStatementTemp = REPLACE(REPLACE(@DropStatement,'',@TableName),'',@ConstraintName) SET @CreateStatementTemp = REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(@CreateStatement,'',@TableName),'',@ColumnName),'',@ConstraintName),'',@ReferencedTableName),'',@ReferencedColumnName) SET @TruncateStatementTemp = REPLACE(@TruncateStatement,'',@TableName) INSERT INTO [Internal_FK_Definition_Storage] SELECT @ConstraintName, @CreateStatementTemp, @DropStatementTemp, @TruncateStatementTemp SET @i = @i + 1 IF @Verbose = 1 PRINT ' > Backing up [' + @ConstraintName + '] from [' + @TableName + ']' END END ELSE PRINT '2. Backup up was recycled from previous execution...' IF @Verbose = 1 PRINT '3. Dropping Foreign Keys...' -- DROP FOREING KEYS SET @i = 1 WHILE (@i <= (SELECT MAX(ID) FROM [Internal_FK_Definition_Storage])) BEGIN SET @ConstraintName = (SELECT FK_Name FROM [Internal_FK_Definition_Storage] WHERE ID = @i) SET @Statement = (SELECT FK_DestructionStatement FROM [Internal_FK_Definition_Storage] WITH (NOLOCK) WHERE ID = @i) IF @Debug = 1 PRINT @Statement ELSE EXEC(@Statement) SET @i = @i + 1 IF @Verbose = 1 PRINT ' > Dropping [' + @ConstraintName + ']' END IF @Verbose = 1 PRINT '4. Truncating Tables...' -- TRUNCATE TABLES -- SzP: commented out as the tables to be truncated might also contain tables that has foreign keys -- to resolve this the stored procedure should be called recursively, but I dont have the time to do it... /* SET @i = 1 WHILE (@i <= (SELECT MAX(ID) FROM [Internal_FK_Definition_Storage])) BEGIN SET @Statement = (SELECT Table_TruncationStatement FROM [Internal_FK_Definition_Storage] WHERE ID = @i) IF @Debug = 1 PRINT @Statement ELSE EXEC(@Statement) SET @i = @i + 1 IF @Verbose = 1 PRINT ' > ' + @Statement END */ IF @Verbose = 1 PRINT ' > TRUNCATE TABLE [' + @TableToTruncate + ']' IF @Debug = 1 PRINT 'TRUNCATE TABLE [' + @TableToTruncate + ']' ELSE EXEC('TRUNCATE TABLE [' + @TableToTruncate + ']') IF @Verbose = 1 PRINT '5. Re-creating Foreign Keys...' -- CREATE FOREING KEYS SET @i = 1 WHILE (@i <= (SELECT MAX(ID) FROM [Internal_FK_Definition_Storage])) BEGIN SET @ConstraintName = (SELECT FK_Name FROM [Internal_FK_Definition_Storage] WHERE ID = @i) SET @Statement = (SELECT FK_CreationStatement FROM [Internal_FK_Definition_Storage] WHERE ID = @i) IF @Debug = 1 PRINT @Statement ELSE EXEC(@Statement) SET @i = @i + 1 IF @Verbose = 1 PRINT ' > Re-creating [' + @ConstraintName + ']' END IF @Verbose = 1 PRINT '6. Process Completed' END 

use el siguiente comando después de eliminar todas las filas en esa tabla usando la instrucción delete

 delete from tablename DBCC CHECKIDENT ('tablename', RESEED, 0) 

EDITAR: syntax corregida para SQL Server

puede seguir este paso, al reseeding table puede eliminar los datos de la tabla.

 delete from table_name dbcc checkident('table_name',reseed,0) 

si se produce algún error, entonces debe resembrar la tabla principal.

Aquí hay un script que escribí para automatizar el proceso. Espero que ayude.

 SET NOCOUNT ON -- GLOBAL VARIABLES DECLARE @i int DECLARE @Debug bit DECLARE @Recycle bit DECLARE @Verbose bit DECLARE @TableName varchar(80) DECLARE @ColumnName varchar(80) DECLARE @ReferencedTableName varchar(80) DECLARE @ReferencedColumnName varchar(80) DECLARE @ConstraintName varchar(250) DECLARE @CreateStatement varchar(max) DECLARE @DropStatement varchar(max) DECLARE @TruncateStatement varchar(max) DECLARE @CreateStatementTemp varchar(max) DECLARE @DropStatementTemp varchar(max) DECLARE @TruncateStatementTemp varchar(max) DECLARE @Statement varchar(max) -- 1 = Will not execute statements SET @Debug = 0 -- 0 = Will not create or truncate storage table -- 1 = Will create or truncate storage table SET @Recycle = 0 -- 1 = Will print a message on every step set @Verbose = 1 SET @i = 1 SET @CreateStatement = 'ALTER TABLE [dbo].[] WITH NOCHECK ADD CONSTRAINT [] FOREIGN KEY([]) REFERENCES [dbo].[] ([])' SET @DropStatement = 'ALTER TABLE [dbo].[] DROP CONSTRAINT []' SET @TruncateStatement = 'TRUNCATE TABLE []' -- Drop Temporary tables DROP TABLE #FKs -- GET FKs SELECT ROW_NUMBER() OVER (ORDER BY OBJECT_NAME(parent_object_id), clm1.name) as ID, OBJECT_NAME(constraint_object_id) as ConstraintName, OBJECT_NAME(parent_object_id) as TableName, clm1.name as ColumnName, OBJECT_NAME(referenced_object_id) as ReferencedTableName, clm2.name as ReferencedColumnName INTO #FKs FROM sys.foreign_key_columns fk JOIN sys.columns clm1 ON fk.parent_column_id = clm1.column_id AND fk.parent_object_id = clm1.object_id JOIN sys.columns clm2 ON fk.referenced_column_id = clm2.column_id AND fk.referenced_object_id= clm2.object_id WHERE OBJECT_NAME(parent_object_id) not in ('//tables that you do not wont to be truncated') ORDER BY OBJECT_NAME(parent_object_id) -- Prepare Storage Table IF Not EXISTS(SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'Internal_FK_Definition_Storage') BEGIN IF @Verbose = 1 PRINT '1. Creating Process Specific Tables...' -- CREATE STORAGE TABLE IF IT DOES NOT EXISTS CREATE TABLE [Internal_FK_Definition_Storage] ( ID int not null identity(1,1) primary key, FK_Name varchar(250) not null, FK_CreationStatement varchar(max) not null, FK_DestructionStatement varchar(max) not null, Table_TruncationStatement varchar(max) not null ) END ELSE BEGIN IF @Recycle = 0 BEGIN IF @Verbose = 1 PRINT '1. Truncating Process Specific Tables...' -- TRUNCATE TABLE IF IT ALREADY EXISTS TRUNCATE TABLE [Internal_FK_Definition_Storage] END ELSE PRINT '1. Process specific table will be recycled from previous execution...' END IF @Recycle = 0 BEGIN IF @Verbose = 1 PRINT '2. Backing up Foreign Key Definitions...' -- Fetch and persist FKs WHILE (@i <= (SELECT MAX(ID) FROM #FKs)) BEGIN SET @ConstraintName = (SELECT ConstraintName FROM #FKs WHERE ID = @i) SET @TableName = (SELECT TableName FROM #FKs WHERE ID = @i) SET @ColumnName = (SELECT ColumnName FROM #FKs WHERE ID = @i) SET @ReferencedTableName = (SELECT ReferencedTableName FROM #FKs WHERE ID = @i) SET @ReferencedColumnName = (SELECT ReferencedColumnName FROM #FKs WHERE ID = @i) SET @DropStatementTemp = REPLACE(REPLACE(@DropStatement,'',@TableName),'',@ConstraintName) SET @CreateStatementTemp = REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(@CreateStatement,'',@TableName),'',@ColumnName),'',@ConstraintName),'',@ReferencedTableName),'',@ReferencedColumnName) SET @TruncateStatementTemp = REPLACE(@TruncateStatement,'',@TableName) INSERT INTO [Internal_FK_Definition_Storage] SELECT @ConstraintName, @CreateStatementTemp, @DropStatementTemp, @TruncateStatementTemp SET @i = @i + 1 IF @Verbose = 1 PRINT ' > Backing up [' + @ConstraintName + '] from [' + @TableName + ']' END END ELSE PRINT '2. Backup up was recycled from previous execution...' IF @Verbose = 1 PRINT '3. Dropping Foreign Keys...' -- DROP FOREING KEYS SET @i = 1 WHILE (@i <= (SELECT MAX(ID) FROM [Internal_FK_Definition_Storage])) BEGIN SET @ConstraintName = (SELECT FK_Name FROM [Internal_FK_Definition_Storage] WHERE ID = @i) SET @Statement = (SELECT FK_DestructionStatement FROM [Internal_FK_Definition_Storage] WITH (NOLOCK) WHERE ID = @i) IF @Debug = 1 PRINT @Statement ELSE EXEC(@Statement) SET @i = @i + 1 IF @Verbose = 1 PRINT ' > Dropping [' + @ConstraintName + ']' END IF @Verbose = 1 PRINT '4. Truncating Tables...' -- TRUNCATE TABLES SET @i = 1 WHILE (@i <= (SELECT MAX(ID) FROM [Internal_FK_Definition_Storage])) BEGIN SET @Statement = (SELECT Table_TruncationStatement FROM [Internal_FK_Definition_Storage] WHERE ID = @i) IF @Debug = 1 PRINT @Statement ELSE EXEC(@Statement) SET @i = @i + 1 IF @Verbose = 1 PRINT ' > ' + @Statement END IF @Verbose = 1 PRINT '5. Re-creating Foreign Keys...' -- CREATE FOREING KEYS SET @i = 1 WHILE (@i <= (SELECT MAX(ID) FROM [Internal_FK_Definition_Storage])) BEGIN SET @ConstraintName = (SELECT FK_Name FROM [Internal_FK_Definition_Storage] WHERE ID = @i) SET @Statement = (SELECT FK_CreationStatement FROM [Internal_FK_Definition_Storage] WHERE ID = @i) IF @Debug = 1 PRINT @Statement ELSE EXEC(@Statement) SET @i = @i + 1 IF @Verbose = 1 PRINT ' > Re-creating [' + @ConstraintName + ']' END IF @Verbose = 1 PRINT '6. Process Completed' 

Encontrado en otra parte de la web

 EXEC sp_MSForEachTable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL' EXEC sp_MSForEachTable 'ALTER TABLE ? DISABLE TRIGGER ALL' -- EXEC sp_MSForEachTable 'DELETE FROM ?' -- Uncomment to execute EXEC sp_MSForEachTable 'ALTER TABLE ? CHECK CONSTRAINT ALL' EXEC sp_MSForEachTable 'ALTER TABLE ? ENABLE TRIGGER ALL' 

Bueno, ya que no encontré ejemplos de la solución muy simple que utilicé, que es:

  1. Soltar clave externa;
  2. Tabla truncada
  3. Recreación de clave foránea

Aquí va:

1) Encuentre el nombre de la clave externa que está causando la falla (por ejemplo: FK_PROBLEM_REASON, con ID campo, de la tabla TABLE_OWNING_CONSTRAINT ) 2) Elimine esa clave de la tabla:

 ALTER TABLE TABLE_OWNING_CONSTRAINT DROP CONSTRAINT FK_PROBLEM_REASON 

3) Truncar la tabla deseada

 TRUNCATE TABLE TABLE_TO_TRUNCATE 

4) Vuelva a agregar la clave a esa primera tabla:

 ALTER TABLE TABLE_OWNING_CONSTRAINT ADD CONSTRAINT FK_PROBLEM_REASON FOREIGN KEY(ID) REFERENCES TABLE_TO_TRUNCATE (ID) 

Eso es.

Si lo entiendo correctamente, lo que quiere hacer es tener un entorno limpio para configurar DB que involucre pruebas de integración.

Mi enfoque aquí sería soltar todo el esquema y recrearlo más tarde.

Razones:

  1. Probablemente ya tenga un script de “crear esquema”. Reutilizarlo para el aislamiento de pruebas es fácil.
  2. Crear un esquema es bastante rápido.
  3. Con ese enfoque, es bastante fácil configurar su secuencia de comandos para que cada dispositivo cree un esquema NUEVO (con un nombre temporal), y luego puede comenzar a ejecutar las pruebas en paralelo, haciendo que la parte más lenta de su suite de pruebas sea mucho más rápida. .

No puede truncar una tabla si no elimina las restricciones. Una deshabilitación tampoco funciona. necesitas dejar todo. He hecho un script que elimina todas las restricciones y luego vuelve a crearlas.

Asegúrese de envolverlo en una transacción;)

 SET NOCOUNT ON GO DECLARE @table TABLE( RowId INT PRIMARY KEY IDENTITY(1, 1), ForeignKeyConstraintName NVARCHAR(200), ForeignKeyConstraintTableSchema NVARCHAR(200), ForeignKeyConstraintTableName NVARCHAR(200), ForeignKeyConstraintColumnName NVARCHAR(200), PrimaryKeyConstraintName NVARCHAR(200), PrimaryKeyConstraintTableSchema NVARCHAR(200), PrimaryKeyConstraintTableName NVARCHAR(200), PrimaryKeyConstraintColumnName NVARCHAR(200) ) INSERT INTO @table(ForeignKeyConstraintName, ForeignKeyConstraintTableSchema, ForeignKeyConstraintTableName, ForeignKeyConstraintColumnName) SELECT U.CONSTRAINT_NAME, U.TABLE_SCHEMA, U.TABLE_NAME, U.COLUMN_NAME FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE U INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS C ON U.CONSTRAINT_NAME = C.CONSTRAINT_NAME WHERE C.CONSTRAINT_TYPE = 'FOREIGN KEY' UPDATE @table SET PrimaryKeyConstraintName = UNIQUE_CONSTRAINT_NAME FROM @table T INNER JOIN INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS R ON T.ForeignKeyConstraintName = R.CONSTRAINT_NAME UPDATE @table SET PrimaryKeyConstraintTableSchema = TABLE_SCHEMA, PrimaryKeyConstraintTableName = TABLE_NAME FROM @table T INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS C ON T.PrimaryKeyConstraintName = C.CONSTRAINT_NAME UPDATE @table SET PrimaryKeyConstraintColumnName = COLUMN_NAME FROM @table T INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE U ON T.PrimaryKeyConstraintName = U.CONSTRAINT_NAME --DROP CONSTRAINT: DECLARE @dynSQL varchar(MAX); DECLARE cur CURSOR FOR SELECT ' ALTER TABLE [' + ForeignKeyConstraintTableSchema + '].[' + ForeignKeyConstraintTableName + '] DROP CONSTRAINT ' + ForeignKeyConstraintName + ' ' FROM @table OPEN cur FETCH cur into @dynSQL WHILE @@FETCH_STATUS = 0 BEGIN exec(@dynSQL) print @dynSQL FETCH cur into @dynSQL END CLOSE cur DEALLOCATE cur --------------------- --HERE GOES YOUR TRUNCATES!!!!! --HERE GOES YOUR TRUNCATES!!!!! --HERE GOES YOUR TRUNCATES!!!!! truncate table your_table --HERE GOES YOUR TRUNCATES!!!!! --HERE GOES YOUR TRUNCATES!!!!! --HERE GOES YOUR TRUNCATES!!!!! --------------------- --ADD CONSTRAINT: DECLARE cur2 CURSOR FOR SELECT ' ALTER TABLE [' + ForeignKeyConstraintTableSchema + '].[' + ForeignKeyConstraintTableName + '] ADD CONSTRAINT ' + ForeignKeyConstraintName + ' FOREIGN KEY(' + ForeignKeyConstraintColumnName + ') REFERENCES [' + PrimaryKeyConstraintTableSchema + '].[' + PrimaryKeyConstraintTableName + '](' + PrimaryKeyConstraintColumnName + ') ' FROM @table OPEN cur2 FETCH cur2 into @dynSQL WHILE @@FETCH_STATUS = 0 BEGIN exec(@dynSQL) print @dynSQL FETCH cur2 into @dynSQL END CLOSE cur2 DEALLOCATE cur2 

truncar no funcionó, borrar + resecar es la mejor salida. En caso de que haya algunos de ustedes que necesiten iterar sobre un gran número de tablas para realizar delete + reseed, es posible que encuentren problemas con algunas tablas que no tienen una columna de identidad, el siguiente código verifica si existe una columna de identidad antes de intentar resembrar

  EXEC ('DELETE FROM [schemaName].[tableName]') IF EXISTS (Select * from sys.identity_columns where object_name(object_id) = 'tableName') BEGIN EXEC ('DBCC CHECKIDENT ([schemaName.tableName], RESEED, 0)') END 
 SET FOREIGN_KEY_CHECKS = 0; truncate table "yourTableName"; SET FOREIGN_KEY_CHECKS = 1; 

Lo siguiente funciona para mí incluso con restricciones de FK, y combina las siguientes respuestas para eliminar solo las tablas especificadas :

  • Reversión automática de transacción
  • Looping a través de una lista separada por comas
  • Ejecución de SQL dynamic (con nombres de tabla de variable)
  • Tabla DELETE y RESEED (en este hilo)

 USE [YourDB]; DECLARE @TransactionName varchar(20) = 'stopdropandroll'; BEGIN TRAN @TransactionName; set xact_abort on; /* automatic rollback https://stackoverflow.com/a/1749788/1037948 */ -- ===== DO WORK // ===== -- dynamic sql placeholder DECLARE @SQL varchar(300); -- LOOP: https://stackoverflow.com/a/10031803/1037948 -- list of things to loop DECLARE @delim char = ';'; DECLARE @foreach varchar(MAX) = 'Table;Names;Separated;By;Delimiter' + @delim + 'AnotherName' + @delim + 'Still Another'; DECLARE @token varchar(MAX); WHILE len(@foreach) > 0 BEGIN -- set current loop token SET @token = left(@foreach, charindex(@delim, @foreach+@delim)-1) -- ======= DO WORK // =========== -- dynamic sql (parentheses are required): https://stackoverflow.com/a/989111/1037948 SET @SQL = 'DELETE FROM [' + @token + ']; DBCC CHECKIDENT (''' + @token + ''',RESEED, 0);'; -- https://stackoverflow.com/a/11784890 PRINT @SQL; EXEC (@SQL); -- ======= // END WORK =========== -- continue loop, chopping off token SET @foreach = stuff(@foreach, 1, charindex(@delim, @foreach+@delim), '') END -- ===== // END WORK ===== -- review and commit SELECT @@TRANCOUNT as TransactionsPerformed, @@ROWCOUNT as LastRowsChanged; COMMIT TRAN @TransactionName; 

Nota:

Creo que aún ayuda declarar las tablas en el orden en que las quiere eliminar (es decir, eliminar dependencias primero). Como se ve en esta respuesta , en lugar de nombres específicos de bucle, puede sustituir todas las tablas por

 EXEC sp_MSForEachTable 'DELETE FROM ?; DBCC CHECKIDENT (''?'',RESEED, 0);'; 

Es mi solución a este problema. Lo usé para alterar PK, pero la idea es la misma. Espero que esto sea útil)

 PRINT 'Script starts' DECLARE @foreign_key_name varchar(255) DECLARE @keycnt int DECLARE @foreign_table varchar(255) DECLARE @foreign_column_1 varchar(255) DECLARE @foreign_column_2 varchar(255) DECLARE @primary_table varchar(255) DECLARE @primary_column_1 varchar(255) DECLARE @primary_column_2 varchar(255) DECLARE @TablN varchar(255) -->> Type the primary table name SET @TablN = '' --------------------------------------------------------------------------------------- ------------------------------ --Here will be created the temporary table with all reference FKs --------------------------------------------------------------------------------------------------------------------- PRINT 'Creating the temporary table' select cast(f.name as varchar(255)) as foreign_key_name , r.keycnt , cast(c.name as varchar(255)) as foreign_table , cast(fc.name as varchar(255)) as foreign_column_1 , cast(fc2.name as varchar(255)) as foreign_column_2 , cast(p.name as varchar(255)) as primary_table , cast(rc.name as varchar(255)) as primary_column_1 , cast(rc2.name as varchar(255)) as primary_column_2 into #ConTab from sysobjects f inner join sysobjects c on f.parent_obj = c.id inner join sysreferences r on f.id = r.constid inner join sysobjects p on r.rkeyid = p.id inner join syscolumns rc on r.rkeyid = rc.id and r.rkey1 = rc.colid inner join syscolumns fc on r.fkeyid = fc.id and r.fkey1 = fc.colid left join syscolumns rc2 on r.rkeyid = rc2.id and r.rkey2 = rc.colid left join syscolumns fc2 on r.fkeyid = fc2.id and r.fkey2 = fc.colid where f.type = 'F' and p.name = @TablN ORDER BY cast(p.name as varchar(255)) --------------------------------------------------------------------------------------------------------------------- --Cursor, below, will drop all reference FKs --------------------------------------------------------------------------------------------------------------------- DECLARE @CURSOR CURSOR /*Fill in cursor*/ PRINT 'Cursor 1 starting. All refernce FK will be droped' SET @CURSOR = CURSOR SCROLL FOR select foreign_key_name , keycnt , foreign_table , foreign_column_1 , foreign_column_2 , primary_table , primary_column_1 , primary_column_2 from #ConTab OPEN @CURSOR FETCH NEXT FROM @CURSOR INTO @foreign_key_name, @keycnt, @foreign_table, @foreign_column_1, @foreign_column_2, @primary_table, @primary_column_1, @primary_column_2 WHILE @@FETCH_STATUS = 0 BEGIN EXEC ('ALTER TABLE ['+@foreign_table+'] DROP CONSTRAINT ['+@foreign_key_name+']') FETCH NEXT FROM @CURSOR INTO @foreign_key_name, @keycnt, @foreign_table, @foreign_column_1, @foreign_column_2, @primary_table, @primary_column_1, @primary_column_2 END CLOSE @CURSOR PRINT 'Cursor 1 finished work' --------------------------------------------------------------------------------------------------------------------- --Here you should provide the chainging script for the primary table --------------------------------------------------------------------------------------------------------------------- PRINT 'Altering primary table begin' TRUNCATE TABLE table_name PRINT 'Altering finished' --------------------------------------------------------------------------------------------------------------------- --Cursor, below, will add again all reference FKs -------------------------------------------------------------------------------------------------------------------- PRINT 'Cursor 2 starting. All refernce FK will added' SET @CURSOR = CURSOR SCROLL FOR select foreign_key_name , keycnt , foreign_table , foreign_column_1 , foreign_column_2 , primary_table , primary_column_1 , primary_column_2 from #ConTab OPEN @CURSOR FETCH NEXT FROM @CURSOR INTO @foreign_key_name, @keycnt, @foreign_table, @foreign_column_1, @foreign_column_2, @primary_table, @primary_column_1, @primary_column_2 WHILE @@FETCH_STATUS = 0 BEGIN EXEC ('ALTER TABLE [' +@foreign_table+ '] WITH NOCHECK ADD CONSTRAINT [' +@foreign_key_name+ '] FOREIGN KEY(['+@foreign_column_1+']) REFERENCES [' +@primary_table+'] (['+@primary_column_1+'])') EXEC ('ALTER TABLE [' +@foreign_table+ '] CHECK CONSTRAINT [' +@foreign_key_name+']') FETCH NEXT FROM @CURSOR INTO @foreign_key_name, @keycnt, @foreign_table, @foreign_column_1, @foreign_column_2, @primary_table, @primary_column_1, @primary_column_2 END CLOSE @CURSOR PRINT 'Cursor 2 finished work' --------------------------------------------------------------------------------------------------------------------- PRINT 'Temporary table droping' drop table #ConTab PRINT 'Finish' 

For MS SQL , at least the newer versions, you can just disable the constrains with code like this:

 ALTER TABLE Orders NOCHECK CONSTRAINT [FK_dbo.Orders_dbo.Customers_Customer_Id] GO TRUNCATE TABLE Customers GO ALTER TABLE Orders WITH CHECK CHECK CONSTRAINT [FK_dbo.Orders_dbo.Customers_Customer_Id] GO 

If none of these answers worked like in my case do this:

  1. Drop constraints
  2. Set all values to allow nulls
  3. Truncate table
  4. Add constraints that were dropped.

¡Buena suerte!

I write the following ways and tried to parameterized them, so you can Run them in a Query document Or Make a useful SP with them easily .

A) Delete

If your table has not millions of records this works good and hasn’t any Alter commands :

 --------------------------------------------------------------- ------------------- Just Fill Parameters Value ---------------- --------------------------------------------------------------- DECLARE @DbName AS NVARCHAR(30) = 'MyDb' --< Db Name DECLARE @Schema AS NVARCHAR(30) = 'dbo' --< Schema DECLARE @TableName AS NVARCHAR(30) = 'Book' --< Table Name ------------------ /Just Fill Parameters Value ---------------- DECLARE @Query AS NVARCHAR(500) = 'Delete FROM ' + @TableName EXECUTE sp_executesql @Query SET @Query=@DbName+'.'+@Schema+'.'+@TableName DBCC CHECKIDENT (@Query,RESEED, 0) 
  • In above answer of mine the method of resolve the mentioned problem in the question is based on @s15199d answer .

B) Truncate

If your table has millions of records or you hasn't any problem with Alter command in your codes, then use this one:

 -- Book Student -- -- | BookId | Field1 | | StudentId | BookId | -- --------------------- ------------------------ -- | 1 | A | | 2 | 1 | -- | 2 | B | | 1 | 1 | -- | 3 | C | | 2 | 3 | --------------------------------------------------------------- ------------------- Just Fill Parameters Value ---------------- --------------------------------------------------------------- DECLARE @DbName AS NVARCHAR(30) = 'MyDb' DECLARE @Schema AS NVARCHAR(30) = 'dbo' DECLARE @TableName_ToTruncate AS NVARCHAR(30) = 'Book' DECLARE @TableName_OfOwnerOfConstraint AS NVARCHAR(30) = 'Student' --< Decelations About FK_Book_Constraint DECLARE @Ref_ColumnName_In_TableName_ToTruncate AS NVARCHAR(30) = 'BookId' --< Decelations About FK_Book_Constraint DECLARE @FK_ColumnName_In_TableOfOwnerOfConstraint AS NVARCHAR(30) = 'Fk_BookId' --< Decelations About FK_Book_Constraint DECLARE @FK_ConstraintName AS NVARCHAR(30) = 'FK_Book_Constraint' --< Decelations About FK_Book_Constraint ------------------ /Just Fill Parameters Value ---------------- DECLARE @Query AS NVARCHAR(2000) SET @Query= 'ALTER TABLE '+@TableName_OfOwnerOfConstraint+' DROP CONSTRAINT '+@FK_ConstraintName EXECUTE sp_executesql @Query SET @Query= 'Truncate Table '+ @TableName_ToTruncate EXECUTE sp_executesql @Query SET @Query= 'ALTER TABLE '+@TableName_OfOwnerOfConstraint+' ADD CONSTRAINT '+@FK_ConstraintName+' FOREIGN KEY('+@FK_ColumnName_In_TableOfOwnerOfConstraint+') REFERENCES '+@TableName_ToTruncate+'('+@Ref_ColumnName_In_TableName_ToTruncate+')' EXECUTE sp_executesql @Query 
  • In above answer of mine the method of resolve the mentioned problem in the question is based on @LauroWolffValenteSobrinho answer .

  • If you have more than one CONSTRAINT then you should append its codes like me to the above query

  • Also you can change the above code base @SerjSagan answer to disable an enable the constraint

In SSMS I had Diagram open showing the Key. After deleting the Key and truncating the file I refreshed then focused back on the Diagram and created an update by clearing then restring an Identity box. Saving the Diagram brought up a Save dialog box, than a “Changes were made in the database while you where working” dialog box, clicking Yes restred the Key, restring it from the latched copy in the Diagram.

If you’re doing this at any sort of a frequency, heck even on a schedule, I would absolutely, unequivocally never use a DML statement. The cost of writing to the transaction log is just to high, and setting the entire database into SIMPLE recovery mode to truncate one table is ridiculous.

The best way, is unfortunately the hard or laborious way. That being:

  • Drop constraints
  • Truncate table
  • Re-create constraints

My process for doing this involves the following steps:

  1. In SSMS right-click on the table in question, and select View Dependencies
  2. Take note of the tables referenced (if any)
  3. Back in object explorer, expand the Keys node and take note of the foreign keys (if any)
  4. Start scripting (drop / truncate / re-create)

Scripts of this nature should be done within a begin tran and commit tran block.

You could try DELETE FROM ; .

The server will show you the name of the restriction and the table, and deleting that table you can delete what you need.

I have just found that you can use TRUNCATE table on a parent table with foreign key constraints on a child as long as you DISABLE the constraints on the child table first. P.ej

Foreign key CONSTRAINT child_par_ref on child table, references PARENT_TABLE

 ALTER TABLE CHILD_TABLE DISABLE CONSTRAINT child_par_ref; TRUNCATE TABLE CHILD_TABLE; TRUNCATE TABLE PARENT_TABLE; ALTER TABLE CHILD_TABLE ENABLE CONSTRAINT child_par_ref; 

La forma más fácil:
1 – Enter in phpmyadmin
2 – Click on table name in left column
3 – Click in Operation (top menu)
4 – Click “Empty the table (TRUNCATE)
5 – Disable box “Enable foreign key checks”
6 – Done!

Link to image tutorial
Tutorial: http://www.imageno.com/wz6gv1wuqajrpic.html
(sorry, I don’t have enough reputation to upload images here :P)

 SET FOREIGN_KEY_CHECKS=0; TRUNCATE table1; TRUNCATE table2; SET FOREIGN_KEY_CHECKS=1; 

reference – truncate foreign key constrained table

Working for me in MYSQL