Não é possível truncar a tabela porque ela está sendo referenciada por uma restrição FOREIGN KEY?

Usando o MSSQL2005, posso truncar uma tabela com uma restrição de chave estrangeira se primeiro truncar a tabela filho (a tabela com a chave primária do relacionamento FK)?

Eu sei que eu também posso

  • Use um DELETE sem uma cláusula where e, em seguida, RESEED a identidade (ou)
  • Remova o FK, trunque a tabela e recrie o FK.

Eu pensei que, enquanto eu truncasse a tabela de filhos antes dos pais, eu ficaria bem sem fazer nenhuma das opções acima, mas estou recebendo este erro:

Não é possível truncar a tabela ‘TableName’ porque está sendo referenciada por uma restrição FOREIGN KEY.

Corrigir; você não pode truncar uma tabela que tenha uma restrição FK.

Normalmente, meu processo para isso é:

  1. Solte as restrições
  2. Trunc a mesa
  3. Recrie as restrições.

(Tudo em uma transação, claro.)

Claro, isso só se aplica se a criança já tiver sido truncada. Caso contrário, eu vou em um caminho diferente, dependendo inteiramente de como meus dados se parecem. (Muitas variables ​​para entrar aqui.)

O pôster original determinou por que esse é o caso; veja esta resposta para mais detalhes.

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

Observe que isso provavelmente não é o que você deseja se tiver milhões de registros, pois é muito lento.

Como TRUNCATE TABLE é um comando DDL , ele não pode verificar se os registros na tabela estão sendo referenciados por um registro na tabela filha.

É por isso que DELETE funciona e TRUNCATE TABLE não: porque o database é capaz de se certificar de que não está sendo referenciado por outro registro.

Sem 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 procedimento armazenado

https://github.com/reduardo7/TableTruncate

Observe que isso provavelmente não é o que você deseja se tiver milhões de registros, pois é muito lento.

A solução @denver_citizen fornecida acima não funcionou para mim, mas eu gostei do espírito, então eu modifiquei algumas coisas:

  • tornou um procedimento armazenado
  • mudou a forma como as foreign keys são preenchidas e recriadas
  • o script original trunca todas as tabelas referenciadas, isso pode causar erro de violação de chave externa quando a tabela referenciada tem outras referências de chave estrangeira. Este script trunca apenas a tabela especificada como parâmetro. Cabe ao usuário, chamar este procedimento armazenado várias vezes em todas as tabelas na ordem correta

Para o benefício do público, aqui está o script atualizado:

 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 o seguinte comando após a exclusão de todas as linhas dessa tabela usando a instrução delete

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

EDIT: syntax corrigida para o SQL Server

Você pode seguir este passo, por nova reseeding table você pode excluir os dados da tabela.

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

Se algum erro ocorrer, você terá que propagar novamente a tabela principal.

Aqui está um script que escrevi para automatizar o processo. Espero que ajude.

 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 em outro lugar na 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' 

Bem, desde que eu não encontrei exemplos da solução muito simples que usei, que é:

  1. Solte a chave estrangeira;
  2. Tabela truncada
  3. Recriar chave estrangeira

Aqui vai:

1) Localize o nome da chave estrangeira que está causando a falha (por exemplo: FK_PROBLEM_REASON, com o ID campo, da tabela TABLE_OWNING_CONSTRAINT ) 2) Remova essa chave da tabela:

 ALTER TABLE TABLE_OWNING_CONSTRAINT DROP CONSTRAINT FK_PROBLEM_REASON 

3) Truncar tabela desejada

 TRUNCATE TABLE TABLE_TO_TRUNCATE 

4) Adicione novamente a chave à primeira tabela:

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

É isso aí.

Se bem entendi, o que você quer fazer é ter um ambiente limpo para ser configurado para o database envolvendo testes de integração.

Minha abordagem aqui seria abandonar todo o esquema e recriá-lo mais tarde.

Razões:

  1. Você provavelmente já tem um script “criar esquema”. Reutilizá-lo para isolamento de teste é fácil.
  2. Criar um esquema é bem rápido.
  3. Com essa abordagem, é muito fácil configurar seu script para que cada equipamento crie um novo esquema (com um nome temporário) e, em seguida, você pode começar a executar testes de teste em paralelo, tornando a parte mais lenta de sua suíte de teste muito mais rápida .

Você não pode truncar uma tabela se não eliminar as restrições. Uma desativação também não funciona. você precisa largar tudo. Eu fiz um script que soltar todos os constrainsts e, em seguida, recriar então.

Certifique-se de envolvê-lo em uma transação;)

 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 

truncate não funcionou para mim, delete + reseed é a melhor saída. Caso haja alguns de vocês que precisam iterar sobre um grande número de tabelas para executar delete + reseed, você pode encontrar problemas com algumas tabelas que não possuem uma coluna de identidade, o código a seguir verifica se a coluna de identidade existe antes de tentar para replantar

  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; 

O seguinte funciona para mim mesmo com restrições de FK e combina as seguintes respostas para descartar apenas as tabelas especificadas :

  • Reversão Automática de Transação
  • Looping através de uma lista separada por vírgulas
  • Executando SQL Dinâmico (com nomes de tabela da variável)
  • Tabela DELETE e RESEED (neste thread)

 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:

Eu acho que ainda ajuda a declarar as tabelas na ordem que você quer que elas sejam apagadas (isto é, matar as dependencies primeiro). Como visto nesta resposta , em vez de dar um loop a nomes específicos, você poderia replace todas as tabelas por

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

É a minha solução para esse problema. Eu usei para alterar PK, mas faço a mesma coisa. Espero que isso seja ú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.

Boa sorte!

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 restoring 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 restored the Key, restoring 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. Por exemplo

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; 

A maneira mais 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