Ne peut pas tronquer une table parce qu'elle est référencée par une contrainte de clé étrangère?
en utilisant MSSQL2005, puis-je tronquer une table avec une contrainte de clé étrangère si je tronque d'abord la table enfant (la table avec la clé primaire de la relation FK)?
je sais que je peux soit
- utilisez un
DELETE
sans une clause oùRESEED
l'identité (ou) - enlever le FK, tronquer la table, et recréer le FK.
j'ai pensé que tant comme j'ai tronqué la table d'enfant avant le parent, je serais d'accord sans faire l'une des options ci-dessus, mais je reçois cette erreur:
ne peut pas tronquer la table 'TableName' parce qu'elle est référencée par une contrainte de clé étrangère.
25 réponses
Correct; vous ne pouvez pas tronquer une table qui a une contrainte FK sur elle.
typiquement mon processus pour cela est:
- Drop les contraintes
- Trunc la table
- recréer les contraintes.
(tous dans une transaction, bien sûr.)
bien sûr, cela ne s'applique que si l'enfant a déjà été tronqué. sinon j'emprunte un chemin différent, dépendant entièrement de l'apparence de mes données. (Trop de variables pour l'obtenir ici.)
l'affiche originale expliquait pourquoi c'est le cas; voir cette réponse pour plus de détails.
DELETE FROM TABLENAME
DBCC CHECKIDENT ('DATABASENAME.dbo.TABLENAME',RESEED, 0)
notez que ce n'est probablement pas ce que vous voudriez si vous aviez des millions+ de disques, car c'est très lent.
parce que TRUNCATE TABLE
est une commande DDL , il ne peut pas vérifier si les enregistrements dans la table sont référencés par un enregistrement dans la table enfant.
C'est pourquoi DELETE
fonctionne et TRUNCATE TABLE
ne fonctionne pas: parce que la base de données est capable de s'assurer qu'il n'est pas référencé par un autre enregistrement.
sans 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])
Comme Procédure Stockée
https://github.com/reduardo7/TableTruncate
Note que ce n'est probablement pas ce que vous voulez si vous avez des millions+ de disques, car c'est très lent.
la solution @denver_citizen fournie ci-dessus n'a pas fonctionné pour moi, mais j'ai aimé l'esprit de celui-ci donc j'ai modifié quelques choses:
- en a fait un procédé stocké
- a changé la façon dont les clés étrangères sont peuplées et recréées
- le script original tronque toutes les tables référencées, ce qui peut causer une erreur de violation de clé étrangère lorsque la table référencée a d'autres références de clé étrangère. Ce script tronque seulement la table spécifiée en paramètre. Il appartient à l'utilisateur, d'appeler cette procédure stockée plusieurs fois sur toutes les tables dans le bon ordre
pour le bénéfice du public voici le script mis à jour:
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].[<tablename>] WITH NOCHECK ADD CONSTRAINT [<constraintname>] FOREIGN KEY([<column>]) REFERENCES [dbo].[<reftable>] ([<refcolumn>])'
SET @DropStatement = 'ALTER TABLE [dbo].[<tablename>] DROP CONSTRAINT [<constraintname>]'
SET @TruncateStatement = 'TRUNCATE TABLE [<tablename>]'
-- 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>',@TableName),'<constraintname>',@ConstraintName)
SET @CreateStatementTemp = REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(@CreateStatement,'<tablename>',@TableName),'<column>',@ColumnName),'<constraintname>',@ConstraintName),'<reftable>',@ReferencedTableName),'<refcolumn>',@ReferencedColumnName)
SET @TruncateStatementTemp = REPLACE(@TruncateStatement,'<tablename>',@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
utiliser la commande suivante après suppression de toutes les lignes dans ce tableau en utilisant l'énoncé supprimer
delete from tablename
DBCC CHECKIDENT ('tablename', RESEED, 0)
EDIT: syntaxe corrigée pour SQL Server
, vous pouvez suivre cette étape,
Par reseeding table
vous pouvez supprimer les données du tableau.
delete from table_name
dbcc checkident('table_name',reseed,0)
si une erreur se produit alors vous devez réensemencer la table primaire.
voici un script que j'ai écrit pour automatiser le processus. J'espère que cela aide.
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].[<tablename>] WITH NOCHECK ADD CONSTRAINT [<constraintname>] FOREIGN KEY([<column>]) REFERENCES [dbo].[<reftable>] ([<refcolumn>])'
SET @DropStatement = 'ALTER TABLE [dbo].[<tablename>] DROP CONSTRAINT [<constraintname>]'
SET @TruncateStatement = 'TRUNCATE TABLE [<tablename>]'
-- 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>',@TableName),'<constraintname>',@ConstraintName)
SET @CreateStatementTemp = REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(@CreateStatement,'<tablename>',@TableName),'<column>',@ColumnName),'<constraintname>',@ConstraintName),'<reftable>',@ReferencedTableName),'<refcolumn>',@ReferencedColumnName)
SET @TruncateStatementTemp = REPLACE(@TruncateStatement,'<tablename>',@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'
trouvé ailleurs sur le 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'
Eh bien, puisque je n'ai pas trouvé exemples de la très simple solution que j'ai utilisé, qui est:
- Drop de clé étrangère;
- table tronquée
- Recréer une clé étrangère
Ici, il va:
1) Trouver le nom de la clé étrangère qui est à l'origine de l'échec (par exemple: FK_PROBLEM_REASON, avec le champ ID
, du tableau 151940920" )
2) supprimer cette clé de la table:
ALTER TABLE TABLE_OWNING_CONSTRAINT DROP CONSTRAINT FK_PROBLEM_REASON
3) table de troncature désirée
TRUNCATE TABLE TABLE_TO_TRUNCATE
4) Ajouter à nouveau la clé de la première table:
ALTER TABLE TABLE_OWNING_CONSTRAINT ADD CONSTRAINT FK_PROBLEM_REASON FOREIGN KEY(ID) REFERENCES TABLE_TO_TRUNCATE (ID)
C'est ça.
si je comprends bien, ce que vous voulez à faire est d'avoir un environnement propre à mettre en place pour DB impliquant des tests d'intégration.
mon approche serait de laisser tomber tout le schéma et de le recréer plus tard.
motifs:
- vous avez probablement déjà un script "create schema". Il est facile de le réutiliser pour l'isolation d'essai.
- Création d'un schéma est assez rapide.
- avec cette approche, il est assez facile de configurer votre script pour que chaque fixture crée un nouveau schéma (avec un nom temporaire), et ensuite vous pouvez commencer à exécuter des fixtures de test en parallèle, rendant la partie la plus lente de votre suite de test beaucoup plus rapide.
Vous ne pouvez pas tronquer une table si vous ne laissez pas tomber les contraintes. Désactiver aussi ne fonctionne pas. vous avez besoin de tout laisser Tomber. j'ai créé un script qui supprime toutes les contraintes et puis le recrée.
assurez-vous de l'envelopper dans une transaction;)
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
SET FOREIGN_KEY_CHECKS = 0;
truncate table "yourTableName";
SET FOREIGN_KEY_CHECKS = 1;
truncate n'a pas fonctionné pour moi, supprimer + Reed est la meilleure solution. Dans le cas où il ya certains d'entre vous là-bas qui ont besoin d'itérer sur un grand nombre de tables pour effectuer supprimer + Reed, vous pourriez rencontrer des problèmes avec certaines tables qui n'a pas une colonne d'identité, le code suivant vérifie si la colonne d'identité existe avant de tenter de Reed
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
ce qui suit fonctionne pour moi, même avec des contraintes FK, et combine les réponses suivantes à supprimer seulement les tableaux spécifiés :
- Opération De Restauration Automatique
- boucle à travers la liste séparée par des virgules
- l'Exécution de SQL Dynamique (avec table des noms de variable)
- supprimer et réensemencer le tableau (dans ce fil)
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;
Remarque:
je pense qu'il est encore utile de déclarer les tables dans l'ordre où vous les voulez supprimées (c'est-à-dire tuer les dépendances d'abord). Comme vu dans cette réponse , plutôt que des noms spécifiques de boucle, vous pourriez remplacer toutes les tables avec
EXEC sp_MSForEachTable 'DELETE FROM ?; DBCC CHECKIDENT (''?'',RESEED, 0);';
c'est ma solution. Je l'ai utilisé pour modifier PK, mais idée la même. J'espère que ce sera 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 ['+@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'
pour MS SQL
, au moins les nouvelles versions, vous pouvez juste désactiver les contraintes avec le code comme ceci:
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
si aucune de ces réponses n'a fonctionné comme dans mon cas, faites ceci:
- supprimer les contraintes
- définir toutes les valeurs pour permettre nulls
- table tronquée
- ajouter les contraintes qui ont été supprimées.
bonne chance!
j'écris les façons suivantes et j'ai essayé de les paramétrer, donc vous pouvez les exécuter dans un Query document
ou faire un utile SP
avec eux facilement .
A) Supprimer
Si votre table n'a pas des millions de documents cela fonctionne bien et n'est pas de Modifier les commandes :
---------------------------------------------------------------
------------------- 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)
- Dans la réponse ci-dessus de la mine de la méthode de résoudre le problème mentionné dans la question, fondée sur @s15199d réponse .
B) Tronqué
si votre table a des millions d'enregistrements ou si vous n'avez aucun problème avec modifier la commande dans vos codes, alors utilisez celui-ci:
-- 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
dans la réponse ci-dessus de la mine la méthode de résoudre le problème mentionné dans la question est basée sur @LauroWolffValenteSobrinho réponse .
si vous avez plus d'une contrainte alors vous devriez ajouter ses codes comme moi à la requête ci-dessus
vous pouvez aussi changer la base de code ci-dessus @SerjSagan réponse pour désactiver la contrainte
dans le SSMS, J'ai ouvert le diagramme montrant la clé. Après avoir supprimé la touche et tronqué le fichier, je me suis concentré sur le diagramme et j'ai créé une mise à jour en effaçant puis en restaurant une boîte D'identité. L'enregistrement du diagramme a amené une boîte de dialogue Enregistrer, qu'un "des changements ont été faits dans la base de données tandis que vous où le travail" boîte de dialogue, en cliquant Oui restauré la clé, la restauration de la copie verrouillée dans le diagramme.
si vous faites cela à n'importe quelle fréquence, même sur un horaire, je dirais absolument, sans équivoque jamais utiliser une déclaration DML. le coût de l'écriture au journal des transactions est juste trop élevé, et mettre la base de données entière dans SIMPLE
mode de récupération pour tronquer une table est ridicule.
la meilleure façon, est malheureusement la manière dure ou laborieuse. Cela étant:
- supprimer les contraintes
- table tronquée
- recréer des contraintes
mon processus pour ce faire comprend les étapes suivantes:
- dans SSMS-cliquez avec le bouton droit de la souris sur la table en question et sélectionnez voir les dépendances
- prendre note des tableaux référencés (le cas échéant)
- retour dans l'Explorateur d'objets, développez les touches nœud et de prendre note des clés étrangères (le cas échéant)
- démarrer le script (abandonner / tronquer / recréer)
les Scripts de cette nature devraient être exécutés dans un bloc begin tran
et commit tran
.
vous pouvez essayer DELETE FROM <your table >;
.
le serveur vous montrera le nom de la restriction et la table, et en supprimant cette table, vous pouvez supprimer ce dont vous avez besoin.
je viens de découvrir que vous pouvez utiliser la table tronquée sur une table parent avec des contraintes de clé étrangère sur un enfant aussi longtemps que vous désactivez les contraintes sur la table enfant d'abord. Par exemple:
contrainte de clé étrangère 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 voie la plus facile:
1-entrer dans phpmyadmin
2-Cliquez sur le nom de la table dans la colonne de gauche
3-Cliquez en opération (menu du haut)
4-Cliquez sur "Vider la table (tronquer)
5 - Désactiver la case "Activer le contrôle des clefs étrangères"
6-fait!
lien vers le tutoriel d'image
tutoriel: http://www.imageno.com/wz6gv1wuqajrpic.html
(désolé, je n'ai pas assez de réputation pour télécharger des images ici :P)
SET FOREIGN_KEY_CHECKS=0;
TRUNCATE table1;
TRUNCATE table2;
SET FOREIGN_KEY_CHECKS=1;
de référence tronquer contrainte de clé étrangère de la table
travailler pour moi à MYSQL