Non è ansible troncare la tabella perché viene referenziata da un vincolo FOREIGN KEY?

Usando MSSQL2005, posso troncare una tabella con un vincolo di chiave esterna se per prima cosa tronca la tabella figlia (la tabella con la chiave primaria della relazione FK)?

So che posso o

  • Utilizzare un DELETE senza una clausola where e quindi RESEED l’id quadro (o)
  • Rimuovere l’FK, troncare la tabella e ricreare l’FK.

Ho pensato che fintanto che ho troncato il tavolo del bambino prima del genitore, starei bene senza fare nessuna delle opzioni sopra, ma sto ricevendo questo errore:

Imansible troncare la tabella ‘TableName’ perché è referenziata da un vincolo FOREIGN KEY.

Corretta; non puoi troncare una tabella che ha un vincolo FK su di essa.

In genere il mio processo per questo è:

  1. Eliminare i vincoli
  2. Trunc il tavolo
  3. Ricrea i vincoli.

(Tutto in una transazione, ovviamente.)

Naturalmente, questo si applica solo se il bambino è già stato troncato. Altrimenti vado in una direzione diversa, dipendente interamente da come sono i miei dati. (Troppe variabili per entrare qui.)

Il poster originale ha determinato PERCHE ‘questo è il caso; vedi questa risposta per maggiori dettagli.

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

Nota che probabilmente non è ciò che vorresti se avessi milioni di record, dato che è molto lento.

Poiché TRUNCATE TABLE è un comando DDL , non è ansible verificare se i record nella tabella vengono referenziati da un record nella tabella figlio.

Questo è il motivo per cui DELETE funziona e TRUNCATE TABLE non lo fa: perché il database è in grado di assicurarsi che non venga referenziato da un altro record.

Senza 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]) 

Come stored procedure

https://github.com/reduardo7/TableTruncate

Nota che probabilmente non è ciò che vorresti se avessi milioni di record, dato che è molto lento.

La soluzione @denver_citizen di cui sopra non ha funzionato per me, ma mi è piaciuto lo spirito e ho modificato alcune cose:

  • ha reso una procedura memorizzata
  • cambiato il modo in cui le chiavi esterne vengono popolate e ricreate
  • lo script originale tronca tutte le tabelle di riferimento, questo può causare un errore di violazione della chiave esterna quando la tabella di riferimento contiene altri riferimenti a chiavi esterne. Questo script tronca solo la tabella specificata come parametro. Spetta all’utente, chiamare questa stored procedure più volte su tutte le tabelle nell’ordine corretto

Per il beneficio del pubblico ecco lo script aggiornato:

 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 

utilizzare il seguente comando dopo l’eliminazione di tutte le righe in quella tabella utilizzando l’istruzione delete

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

EDIT: syntax corretta per SQL Server

puoi seguire questo passaggio, Per reseeding table puoi cancellare i dati della tabella.

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

se viene visualizzato un errore, è necessario eseguire il seeding della tabella principale.

Ecco una sceneggiatura che ho scritto per automatizzare il processo. Spero possa essere d’aiuto.

 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' 

Trovato altrove sul 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' 

Bene, dal momento che non ho trovato esempi della soluzione molto semplice che ho usato, che è:

  1. Rilasciare chiave esterna;
  2. Troncare la tabella
  3. Ricrea la chiave esterna

Eccolo:

1) Trova il nome della chiave esterna che causa l’errore (ad esempio: FK_PROBLEM_REASON, con ID campo, dalla tabella TABLE_OWNING_CONSTRAINT ) 2) Rimuovi quella chiave dalla tabella:

 ALTER TABLE TABLE_OWNING_CONSTRAINT DROP CONSTRAINT FK_PROBLEM_REASON 

3) Truncate la tabella desiderata

 TRUNCATE TABLE TABLE_TO_TRUNCATE 

4) Aggiungere nuovamente la chiave alla prima tabella:

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

Questo è tutto.

Se ho capito bene, quello che vuoi fare è avere un ambiente pulito da configurare per il DB che includa test di integrazione.

Il mio approccio qui sarebbe quello di eliminare l’intero schema e ricrearlo in seguito.

Motivi:

  1. Probabilmente hai già uno script “crea schema”. Riutilizzarlo per l’isolamento del test è facile.
  2. La creazione di uno schema è piuttosto veloce.
  3. Con questo approccio, è abbastanza semplice impostare lo script in modo che ogni fixture crei uno schema NEW (con un nome temporaneo), quindi è ansible iniziare a eseguire test-fix in parallelo, rendendo la parte più lenta della suite di test molto più veloce .

Non è ansible troncare una tabella se non si eliminano i vincoli. Anche una disabilitazione non funziona. devi far cadere tutto. Ho creato uno script che elimina tutti i vincoli e quindi ricrea in seguito.

Assicurati di avvolgerlo in una transazione;)

 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 non ha funzionato per me, eliminare + reseed è la migliore via d’uscita. Nel caso ci siano alcuni di voi là fuori che hanno bisogno di iterare su un numero enorme di tabelle per eseguire delete + reseed, è ansible che si verifichino problemi con alcune tabelle che non hanno una colonna identity, il seguente codice controlla se la colonna identity esiste prima di tentare riseminato

  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; 

Ciò che segue funziona anche con i vincoli FK e combina le seguenti risposte per eliminare solo le tabelle specificate :

  • Transazione Rollback automatico
  • Effettuare il ciclo attraverso l’elenco separato da virgole
  • Esecuzione di SQL dinamico (con nomi di tabelle da variabile)
  • Tabella DELETE e RESEED (in questa discussione)

 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, @[email protected])-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, @[email protected]), '') END -- ===== // END WORK ===== -- review and commit SELECT @@TRANCOUNT as TransactionsPerformsd, @@ROWCOUNT as LastRowsChanged; COMMIT TRAN @TransactionName; 

Nota:

Penso che aiuti ancora a dichiarare le tabelle nell’ordine in cui le vuoi eliminate (ad es. Prima le dipendenze di kill). Come si vede in questa risposta , piuttosto che i nomi specifici del ciclo è ansible sostituire tutte le tabelle con

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

È la mia soluzione a questo problema. L’ho usato per alterare PK, ma l’idea è la stessa. Spero che questo sia utile)

 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 ['[email protected]_table+'] DROP CONSTRAINT ['[email protected]_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 [' [email protected]_table+ '] WITH NOCHECK ADD CONSTRAINT [' [email protected]_key_name+ '] FOREIGN KEY(['[email protected]_column_1+']) REFERENCES [' [email protected]_table+'] (['[email protected]_column_1+'])') EXEC ('ALTER TABLE [' [email protected]_table+ '] CHECK CONSTRAINT [' [email protected]_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.

In bocca al lupo!

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 @[email protected]+'.'[email protected]+'.'[email protected] 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 '[email protected]_OfOwnerOfConstraint+' DROP CONSTRAINT '[email protected]_ConstraintName EXECUTE sp_executesql @Query SET @Query= 'Truncate Table '+ @TableName_ToTruncate EXECUTE sp_executesql @Query SET @Query= 'ALTER TABLE '[email protected]_OfOwnerOfConstraint+' ADD CONSTRAINT '[email protected]_ConstraintName+' FOREIGN KEY('[email protected]_ColumnName_In_TableOfOwnerOfConstraint+') REFERENCES '[email protected]_ToTruncate+'('[email protected]_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. Per esempio

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 via più facile:
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