sql-server - truncate命令 - truncate table table_name




无法截断表,因为它正在被FOREIGN KEY约束引用? (17)

SET FOREIGN_KEY_CHECKS = 0;

截断表“tableName”;

SET FOREIGN_KEY_CHECKS = 1;

使用MSSQL2005,如果我首先截断子表(具有FK关系主键的表),是否可以截断带有外键约束的表?

我知道我也可以

  • 使用没有where子句的DELETE ,然后RESEED身份(或)
  • 删除FK,截断表并重新创建FK。

我认为,只要我在父母之前截断了子表,如果没有执行上述任一选项,我都会好起来的,但是我收到了这个错误:

无法截断表'TableName',因为它正在被FOREIGN KEY约束引用。


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

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


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


The easiest way:
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)


上面提供的解决方案@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

以下工作对我来说即使使用FK约束,也会将以下答案组合到只删除指定的表中

  • 事务自动回滚
  • 通过逗号分隔列表循环
  • 执行动态SQL(使用变量中的表名)
  • share (在这个线程中)
USE [YourDB];

DECLARE @TransactionName varchar(20) = 'stopdropandroll';

BEGIN TRAN @TransactionName;
set xact_abort on; /* automatic rollback https://.com/a/1749788/1037948 */
    -- ===== DO WORK // =====

    -- dynamic sql placeholder
    DECLARE @SQL varchar(300);

    -- LOOP: https://.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://.com/a/989111/1037948
        SET @SQL = 'DELETE FROM [' + @token + ']; DBCC CHECKIDENT (''' + @token + ''',RESEED, 0);'; -- https://.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 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的语法


在网络上的其他地方找到

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'

如果我理解正确,你想要做的是为涉及集成测试的数据库设置一个干净的环境。

我的方法是删除整个模式并在稍后重新创建。

原因:

  1. 您可能已经有了“创建模式”脚本。 重新使用它进行测试隔离很容易。
  2. 创建一个模式非常快。
  3. 使用这种方法,设置脚本让每个灯具创建一个NEW模式(使用临时名称)非常简单,然后您可以开始并行运行测试灯具,从而使测试套件中速度最慢的部分快得多。

截断没有为我工作,删除+种子是最好的出路。 如果有些人需要迭代大量表来执行delete + 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

没有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])

作为存储过程

https://github.com/reduardo7/TableTruncate

请注意 ,如果您拥有数百万条记录,则这可能不是您想要的,因为它非常缓慢。


由于TRUNCATE TABLEDDL命令 ,因此它无法检查表中的记录是否TRUNCATE TABLE中的记录引用。

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


这是我解决这个问题的方法。 我用它来改变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 ['[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'

那么,因为我没有找到我使用的非常简单的解决方案的例子 ,它是:

  1. 丢弃外键;
  2. 截断表
  3. 重新创建外键

这里是:

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)

而已。






foreign-keys