小开

最佳答案

正确的;你不能截断一个有FK约束的表。

通常我的处理方法是:

去掉约束
截断表格
重新创建约束。

(当然，这一切都在一笔交易中。)

当然，这只适用于子节点已经被截断。否则我走不同的路线，完全取决于我的数据看起来像什么。(变量太多，不便在此赘述。)

最初的海报决定了为什么会这样;更多细节见这个答案。

小开

因为TRUNCATE TABLE是DDL命令，所以它不能检查表中的记录是否被子表中的记录引用。

这就是为什么DELETE有效而TRUNCATE TABLE无效:因为数据库能够确保它没有被另一条记录引用。

小开

如果我理解正确的话，您想要要做的是为涉及集成测试的DB设置一个干净的环境。

我在这里的方法是放弃整个模式，稍后再重新创建它。

原因:

您可能已经有了“创建模式”脚本。重用它进行测试隔离很容易。
创建模式非常快。
使用这种方法，可以很容易地设置脚本，让每个fixture创建一个NEW模式(使用临时名称)，然后开始并行运行测试fixture，使测试套件中最慢的部分更快。

小开

下面是我写的一个脚本，以使这个过程自动化。我希望这能有所帮助。

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'

小开

可以在网上其他地方找到

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'

小开

你可以试试DELETE FROM <your table >;。

服务器将向您显示限制的名称和表，删除该表可以删除所需的内容。

小开

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

注意，如果您有数百万条以上的记录，这可能不是您想要的，因为它非常慢。

小开

这是我对这个问题的解决方案。我用它来改变PK，但想法是一样的。希望这将是有用的)

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'

小开

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

参考- 截断外键约束表

在MYSQL为我工作

小开

上面@denver_citizen提供的解决方案并不适合我，但我喜欢它的精神，所以我修改了一些东西:

使其成为存储过程
更改了外键填充和重新创建的方式
原始脚本截断所有引用的表，当引用的表有其他外键引用时，这可能导致违反外键错误。这个脚本只截断作为参数指定的表。由用户决定是否在所有表上以正确的顺序多次调用此存储过程

为了公众的利益，以下是更新后的脚本:

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

小开

如果不删除约束，就不能截断表。disable也不行。你需要放下一切。我已经做了一个脚本，删除所有约束，然后重新创建。

请确保将其包装在事务中;)

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

小开

对于MS SQL，至少是更新的版本，你可以用这样的代码禁用约束:

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

小开

以下为我工作，即使有FK约束，并将以下答案结合到只删除指定的表:

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;

注意:

我认为这仍然有助于按照你想要删除的顺序声明表(即先删除依赖项)。正如在这个答案中看到的，您可以用所有表替换特定的名称，而不是循环特定的名称

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

小开

在使用delete语句删除表中的所有行之后，使用以下命令

delete from tablename


DBCC CHECKIDENT ('tablename', RESEED, 0)

编辑:修正的SQL Server语法

小开

我刚刚发现你可以在父表上使用TRUNCATE表，在子表上使用外键约束，只要你先禁用孩子表上的约束。例如< / p >

子表上的外键约束child_par_ref引用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;

小开

你可以遵循这个步骤，通过reseeding table可以删除表中的数据

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

如果出现错误，则必须重新播种主表。

小开

好吧，因为我没有找到我使用的非常简单的解决方案的例子，这是:

删除外键;
截断表
重新创建外键

是这样的:

1)找到导致失败的外键名称(例如:FK_PROBLEM_REASON，字段ID，来自表TABLE_OWNING_CONSTRAINT) 2)从表中删除该键:

ALTER TABLE TABLE_OWNING_CONSTRAINT DROP CONSTRAINT FK_PROBLEM_REASON

3)截断招聘表

TRUNCATE TABLE TABLE_TO_TRUNCATE

4)重新添加第一个表的键:

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

就是这样。

小开

如果这些答案都不像我的情况那样有效，那就这样做:

减少约束
将所有值设置为允许为空
截断表
添加已删除的约束。

好运！

小开

没有# EYZ0

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

作为存储过程

https://github.com/reduardo7/TableTruncate

请注意，如果你有数百万+的记录，这可能不是你想要的，因为它非常慢。

小开

在SSMS中，我打开了显示密钥的图表。在删除Key和截断文件之后，我刷新了，然后专注于图表，并通过清除和恢复标识框创建了一个更新。保存关系图会出现一个保存对话框，然后出现“当您在工作时，数据库中发生了更改”对话框，单击Yes恢复了密钥，从关系图中的锁定副本恢复它。

小开

截断不适合我，删除+重新播种是最好的方法。如果你们中的一些人需要迭代大量的表来执行delete + reseed，你可能会遇到一些没有标识列的表的问题，下面的代码在尝试reseed

之前检查标识列是否存在

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

小开

最简单的方法:
1 -输入phpmyadmin
2 -点击左列的表名
3 -单击“操作(顶部菜单)
” 4 -单击“清空表格(TRUNCATE)
” 5 -禁用框“启用外键检查”
. 0 6 -完成!< / p >

图像教程链接
# EYZ0 # EYZ1
(对不起，我没有足够的声誉在这里上传图片:P)

小开

如果您以某种频率这样做，甚至是在计划中，我将绝对不要使用DML语句。写入事务日志的成本非常高，并且将整个数据库设置为SIMPLE恢复模式以截断一个表是荒谬的。

最好的办法，不幸是艰苦或费力的办法。这是:

减少约束
截断表
重新创建约束

我这样做的过程包括以下步骤:

在SSMS中，右键单击有问题的表，并选择视图的依赖关系
记下引用的表格(如果有的话)
回到对象资源管理器，展开键节点并注意外键(如果有的话)
开始编写脚本(删除/截断/重新创建)

应该这样的脚本可以在begin tran和commit tran块中完成。

小开

我写了下面的方法，并试图参数化它们，所以在Query document或用它们轻松制作一个有用的SP中运行它们。

一)删除

如果你的表不是有几百万条记录吗这工作得很好和没有任何Alter命令:

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

在我上面的回答中，解决问题中提到的问题的方法是基于@s15199d 回答。

B)截断

如果你的表有数百万条记录或你没有任何问题与修改命令在你的代码，然后使用这一个:

--   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

在上面的回答中，我解决问题中提到的问题的方法是基于@LauroWolffValenteSobrinho answer。

如果你有不止一个约束，那么你应该把它的代码像我一样附加到上面的查询

你也可以改变上面的代码基@SerjSagan answer来禁用或启用约束

小开

正在删除外键约束并截断表

.

这只是MySQL

SET FOREIGN_KEY_CHECKS = 0;


truncate table "yourTableName";


SET FOREIGN_KEY_CHECKS = 1;

小开

@denver_citizen和@Peter Szanto的回答对我不太适用，但我修改了它们，以解释:

组合键
“删除”和“更新”动作
重新添加时检查索引
dbo以外的模式
同时处理多个表

DECLARE @Debug bit = 0;


-- List of tables to truncate
select
SchemaName, Name
into #tables
from (values
('schema', 'table')
,('schema2', 'table2')
) as X(SchemaName, Name)




BEGIN TRANSACTION TruncateTrans;


with foreignKeys AS (
SELECT
SCHEMA_NAME(fk.schema_id) as SchemaName
,fk.Name as ConstraintName
,OBJECT_NAME(fk.parent_object_id) as TableName
,SCHEMA_NAME(t.SCHEMA_ID) as ReferencedSchemaName
,OBJECT_NAME(fk.referenced_object_id) as ReferencedTableName
,fc.constraint_column_id
,COL_NAME(fk.parent_object_id, fc.parent_column_id) AS ColumnName
,COL_NAME(fk.referenced_object_id, fc.referenced_column_id) as ReferencedColumnName
,fk.delete_referential_action_desc
,fk.update_referential_action_desc
FROM sys.foreign_keys AS fk
JOIN sys.foreign_key_columns AS fc
ON fk.object_id = fc.constraint_object_id
JOIN #tables tbl
ON OBJECT_NAME(fc.referenced_object_id) = tbl.Name
JOIN sys.tables t on OBJECT_NAME(t.object_id) = tbl.Name
and SCHEMA_NAME(t.schema_id) = tbl.SchemaName
and t.OBJECT_ID = fc.referenced_object_id
)






select
quotename(fk.ConstraintName) AS ConstraintName
,quotename(fk.SchemaName) + '.' + quotename(fk.TableName) AS TableName
,quotename(fk.ReferencedSchemaName) + '.' + quotename(fk.ReferencedTableName) AS ReferencedTableName
,replace(fk.delete_referential_action_desc, '_', ' ') AS DeleteAction
,replace(fk.update_referential_action_desc, '_', ' ') AS UpdateAction
,STUFF((
SELECT ',' + quotename(fk2.ColumnName)
FROM foreignKeys fk2
WHERE fk2.ConstraintName = fk.ConstraintName and fk2.SchemaName = fk.SchemaName
ORDER BY fk2.constraint_column_id
FOR XML PATH('')
),1,1,'') AS ColumnNames
,STUFF((
SELECT ',' + quotename(fk2.ReferencedColumnName)
FROM foreignKeys fk2
WHERE fk2.ConstraintName = fk.ConstraintName and fk2.SchemaName = fk.SchemaName
ORDER BY fk2.constraint_column_id
FOR XML PATH('')
),1,1,'') AS ReferencedColumnNames
into #FKs
from foreignKeys fk
GROUP BY fk.SchemaName, fk.ConstraintName, fk.TableName, fk.ReferencedSchemaName, fk.ReferencedTableName, fk.delete_referential_action_desc, fk.update_referential_action_desc






-- Drop FKs
select
identity(int,1,1) as ID,
'ALTER TABLE ' + fk.TableName + ' DROP CONSTRAINT ' + fk.ConstraintName AS script
into #scripts
from #FKs fk


-- Truncate
insert into #scripts
select distinct
'TRUNCATE TABLE ' + quotename(tbl.SchemaName) + '.' + quotename(tbl.Name) AS script
from #tables tbl


-- Recreate
insert into #scripts
select
'ALTER TABLE ' + fk.TableName +
' WITH CHECK ADD CONSTRAINT ' + fk.ConstraintName +
' FOREIGN KEY ('+ fk.ColumnNames +')' +
' REFERENCES ' + fk.ReferencedTableName +' ('+ fk.ReferencedColumnNames +')' +
' ON DELETE ' + fk.DeleteAction COLLATE Latin1_General_CI_AS_KS_WS + ' ON UPDATE ' + fk.UpdateAction COLLATE Latin1_General_CI_AS_KS_WS AS script
from #FKs fk




DECLARE @script nvarchar(MAX);


DECLARE curScripts CURSOR FOR
select script
from #scripts
order by ID


OPEN curScripts


WHILE 1=1 BEGIN
FETCH NEXT FROM curScripts INTO @script
IF @@FETCH_STATUS != 0 BREAK;


print @script;
IF @Debug = 0
EXEC (@script);
END
CLOSE curScripts
DEALLOCATE curScripts




drop table #scripts
drop table #FKs
drop table #tables




COMMIT TRANSACTION TruncateTrans;

小开

删除然后重置自动增量:

delete from tablename;

然后

ALTER TABLE tablename AUTO_INCREMENT = 1;

小开

唯一的方法是在截断之前删除外键。截断数据之后，必须重新创建索引。

下面的脚本生成删除所有外键约束所需的SQL。

DECLARE @drop NVARCHAR(MAX) = N'';


SELECT @drop += N'
ALTER TABLE ' + QUOTENAME(cs.name) + '.' + QUOTENAME(ct.name)
+ ' DROP CONSTRAINT ' + QUOTENAME(fk.name) + ';'
FROM sys.foreign_keys AS fk
INNER JOIN sys.tables AS ct
ON fk.parent_object_id = ct.[object_id]
INNER JOIN sys.schemas AS cs
ON ct.[schema_id] = cs.[schema_id];


SELECT @drop

接下来，下面的脚本生成重新创建外键所需的SQL。

DECLARE @create NVARCHAR(MAX) = N'';


SELECT @create += N'
ALTER TABLE '
+ QUOTENAME(cs.name) + '.' + QUOTENAME(ct.name)
+ ' ADD CONSTRAINT ' + QUOTENAME(fk.name)
+ ' FOREIGN KEY (' + STUFF((SELECT ',' + QUOTENAME(c.name)
-- get all the columns in the constraint table
FROM sys.columns AS c
INNER JOIN sys.foreign_key_columns AS fkc
ON fkc.parent_column_id = c.column_id
AND fkc.parent_object_id = c.[object_id]
WHERE fkc.constraint_object_id = fk.[object_id]
ORDER BY fkc.constraint_column_id
FOR XML PATH(N''), TYPE).value(N'.[1]', N'nvarchar(max)'), 1, 1, N'')
+ ') REFERENCES ' + QUOTENAME(rs.name) + '.' + QUOTENAME(rt.name)
+ '(' + STUFF((SELECT ',' + QUOTENAME(c.name)
-- get all the referenced columns
FROM sys.columns AS c
INNER JOIN sys.foreign_key_columns AS fkc
ON fkc.referenced_column_id = c.column_id
AND fkc.referenced_object_id = c.[object_id]
WHERE fkc.constraint_object_id = fk.[object_id]
ORDER BY fkc.constraint_column_id
FOR XML PATH(N''), TYPE).value(N'.[1]', N'nvarchar(max)'), 1, 1, N'') + ');'
FROM sys.foreign_keys AS fk
INNER JOIN sys.tables AS rt -- referenced table
ON fk.referenced_object_id = rt.[object_id]
INNER JOIN sys.schemas AS rs
ON rt.[schema_id] = rs.[schema_id]
INNER JOIN sys.tables AS ct -- constraint table
ON fk.parent_object_id = ct.[object_id]
INNER JOIN sys.schemas AS cs
ON ct.[schema_id] = cs.[schema_id]
WHERE rt.is_ms_shipped = 0 AND ct.is_ms_shipped = 0;


SELECT @create

运行生成的脚本删除所有外键，截断表，然后运行生成的脚本重新创建所有外键。

查询来自在这里。

小开

这是一个使用实体框架的人的例子

需要重置的表:Foo
另一个表依赖于:Bar
表Foo上的约束列:FooColumn

表Bar上的约束列:BarColumn

public override void Down()
{
DropForeignKey("dbo.Bar", "BarColumn", "dbo.Foo");
Sql("TRUNCATE TABLE Foo");
AddForeignKey("dbo.Bar", "BarColumn", "dbo.Foo", "FooColumn", cascadeDelete: true);
}

不能截断表，因为它是由一个外键约束引用?

原因:

作为存储过程

一)删除

B)截断