sql server - ver - ¿No se puede truncar la tabla porque una restricción FOREIGN KEY hace referencia a ella?

restricciones de llave primaria

Question

Con MSSQL2005, ¿puedo truncar una tabla con una restricción de clave externa si primero trunca la tabla secundaria (la tabla con la clave principal de la relación FK)?

Sé que puedo tampoco

• Use un DELETE sin una cláusula where y luego RESEED la identidad (o)
• Quite el FK, trunca la tabla y vuelva a crear el FK.

Pensé que mientras truncara la tabla secundaria antes que el padre, estaría bien sin hacer ninguna de las opciones anteriores, pero recibo este error:

No se puede truncar la tabla ''TableName'' porque se hace referencia a ella por una restricción FOREIGN KEY.

Answer 1

Aquí hay un script que escribí para automatizar el proceso. Espero que ayude.

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

Answer 2

Bueno, como no encontré ejemplos de la solución muy simple que usé, que es:

1. Soltar clave externa;
2. Mesa truncada
3. Reconstruir clave externa

Aquí va:

1) Encuentre el nombre de la clave foránea que está causando el error (por ejemplo: FK_PROBLEM_REASON, con ID campo, de la tabla TABLE_OWNING_CONSTRAINT ) 2) Quite esa clave de la tabla:

ALTER TABLE TABLE_OWNING_CONSTRAINT DROP CONSTRAINT FK_PROBLEM_REASON

3) Truncar la tabla deseada

TRUNCATE TABLE TABLE_TO_TRUNCATE

4) Vuelva a agregar la clave a esa primera tabla:

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

Eso es.

Answer 3

Correcto; no puede truncar una tabla que tenga una restricción FK en ella.

Típicamente mi proceso para esto es:

1. Soltar las restricciones
2. Cortar la mesa
3. Recrear las restricciones.

(Todo en una transacción, por supuesto.)

Por supuesto, esto solo se aplica si el niño ya ha sido truncado. De lo contrario, tomo una ruta diferente, dependiendo completamente de cómo se ven mis datos. (Demasiadas variables para entrar aquí).

El cartel original determinó POR QUÉ este es el caso; Vea esta respuesta para más detalles.

Answer 4

Debido a que TRUNCATE TABLE es un comando DDL , no puede verificar si los registros en la tabla están siendo referenciados por un registro en la tabla secundaria.

Esta es la razón por la que DELETE funciona y TRUNCATE TABLE no: porque la base de datos es capaz de asegurarse de que otro registro no haga referencia a ella.

Answer 5

Encontrado en otro lugar en la 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''

Answer 6

Es mi solución de este problema. Lo utilicé para alterar PK, pero idea lo mismo. Espero que esto sea útil)

PRINT ''Script starts''

DECLARE @foreign_key_name varchar(255)
DECLARE @keycnt int
DECLARE @foreign_table varchar(255)
DECLARE @foreign_column_1 varchar(255)
DECLARE @foreign_column_2 varchar(255)
DECLARE @primary_table varchar(255)
DECLARE @primary_column_1 varchar(255)
DECLARE @primary_column_2 varchar(255)
DECLARE @TablN varchar(255)

-->> Type the primary table name
SET @TablN = ''''
---------------------------------------------------------------------------------------    ------------------------------
--Here will be created the temporary table with all reference FKs
---------------------------------------------------------------------------------------------------------------------
PRINT ''Creating the temporary table''
select cast(f.name  as varchar(255)) as foreign_key_name
    , r.keycnt
    , cast(c.name as  varchar(255)) as foreign_table
    , cast(fc.name as varchar(255)) as  foreign_column_1
    , cast(fc2.name as varchar(255)) as foreign_column_2
    , cast(p.name as varchar(255)) as primary_table
    , cast(rc.name as varchar(255))  as primary_column_1
    , cast(rc2.name as varchar(255)) as  primary_column_2
    into #ConTab
    from sysobjects f
    inner join sysobjects c on  f.parent_obj = c.id 
    inner join sysreferences r on f.id =  r.constid
    inner join sysobjects p on r.rkeyid = p.id
    inner  join syscolumns rc on r.rkeyid = rc.id and r.rkey1 = rc.colid
    inner  join syscolumns fc on r.fkeyid = fc.id and r.fkey1 = fc.colid
    left join  syscolumns rc2 on r.rkeyid = rc2.id and r.rkey2 = rc.colid
    left join  syscolumns fc2 on r.fkeyid = fc2.id and r.fkey2 = fc.colid
    where f.type =  ''F'' and p.name = @TablN
 ORDER BY cast(p.name as varchar(255))
---------------------------------------------------------------------------------------------------------------------
--Cursor, below, will drop all reference FKs
---------------------------------------------------------------------------------------------------------------------
DECLARE @CURSOR CURSOR
/*Fill in cursor*/

PRINT ''Cursor 1 starting. All refernce FK will be droped''

SET @CURSOR  = CURSOR SCROLL
FOR
select foreign_key_name
    , keycnt
    , foreign_table
    , foreign_column_1
    , foreign_column_2
    , primary_table
    , primary_column_1
    , primary_column_2
    from #ConTab

OPEN @CURSOR

FETCH NEXT FROM @CURSOR INTO @foreign_key_name, @keycnt, @foreign_table,         @foreign_column_1, @foreign_column_2, 
                        @primary_table, @primary_column_1, @primary_column_2

WHILE @@FETCH_STATUS = 0
BEGIN

    EXEC (''ALTER TABLE [''+@foreign_table+''] DROP CONSTRAINT [''+@foreign_key_name+'']'')

FETCH NEXT FROM @CURSOR INTO @foreign_key_name, @keycnt, @foreign_table, @foreign_column_1, @foreign_column_2, 
                         @primary_table, @primary_column_1, @primary_column_2
END
CLOSE @CURSOR
PRINT ''Cursor 1 finished work''
---------------------------------------------------------------------------------------------------------------------
--Here you should provide the chainging script for the primary table
---------------------------------------------------------------------------------------------------------------------

PRINT ''Altering primary table begin''

TRUNCATE TABLE table_name

PRINT ''Altering finished''

---------------------------------------------------------------------------------------------------------------------
--Cursor, below, will add again all reference FKs
--------------------------------------------------------------------------------------------------------------------

PRINT ''Cursor 2 starting. All refernce FK will added''
SET @CURSOR  = CURSOR SCROLL
FOR
select foreign_key_name
    , keycnt
    , foreign_table
    , foreign_column_1
    , foreign_column_2
    , primary_table
    , primary_column_1
    , primary_column_2
    from #ConTab

OPEN @CURSOR

FETCH NEXT FROM @CURSOR INTO @foreign_key_name, @keycnt, @foreign_table, @foreign_column_1, @foreign_column_2, 
                         @primary_table, @primary_column_1, @primary_column_2

WHILE @@FETCH_STATUS = 0
BEGIN

    EXEC (''ALTER TABLE ['' +@foreign_table+ ''] WITH NOCHECK ADD  CONSTRAINT ['' +@foreign_key_name+ ''] FOREIGN KEY([''+@foreign_column_1+''])
        REFERENCES ['' +@primary_table+''] ([''+@primary_column_1+''])'')

    EXEC (''ALTER TABLE ['' +@foreign_table+ ''] CHECK CONSTRAINT ['' +@foreign_key_name+'']'')

FETCH NEXT FROM @CURSOR INTO @foreign_key_name, @keycnt, @foreign_table, @foreign_column_1, @foreign_column_2, 
                         @primary_table, @primary_column_1, @primary_column_2
END
CLOSE @CURSOR
PRINT ''Cursor 2 finished work''
---------------------------------------------------------------------------------------------------------------------
PRINT ''Temporary table droping''
drop table #ConTab
PRINT ''Finish''

Answer 7

La solución @denver_citizen proporcionada anteriormente no funcionó para mí, pero me gustó su espíritu, así que modifiqué algunas cosas:

• lo convirtió en un procedimiento almacenado
• cambió la forma en que se rellenan y recrean las claves foráneas
• la secuencia de comandos original trunca todas las tablas a las que se hace referencia, lo que puede causar un error de violación de clave externa cuando la tabla a la que se hace referencia tiene otras referencias de clave externa. Este script trunca solo la tabla especificada como parámetro. Depende del usuario, llamar a este procedimiento almacenado varias veces en todas las tablas en el orden correcto

Para el beneficio del público aquí está el script actualizado:

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

Answer 8

Lo siguiente funciona para mí, incluso con restricciones FK, y combina las siguientes respuestas para eliminar solo las tablas especificadas :

• Transacción automática Rollback
• Bucle a través de la lista separada por comas
• Ejecutando SQL Dinámico (con nombres de tablas desde variables)
• share (en este hilo)

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, @foreach+@delim)-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, @foreach+@delim), '''')
    END

    -- ===== // END WORK =====
-- review and commit
SELECT @@TRANCOUNT as TransactionsPerformed, @@ROWCOUNT as LastRowsChanged;
COMMIT TRAN @TransactionName;

Nota:

Creo que todavía es útil declarar las tablas en el orden en que desea eliminarlas (es decir, eliminar primero las dependencias). Como se ve en esta respuesta , en lugar de nombres específicos de bucle, podría sustituir todas las tablas con

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

Answer 9

No puede truncar una tabla si no elimina las restricciones. Una desactivación tampoco funciona. Necesitas soltar todo. He hecho un script que elimina todos los constreñimientos y luego los recrearé.

Asegúrese de envolverlo en una transacción;)

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

Answer 10

Si comprendo correctamente, lo que desea hacer es tener un entorno limpio que se configure para la base de datos que incluya pruebas de integración.

Mi enfoque aquí sería eliminar todo el esquema y volver a crearlo más tarde.

Razones:

1. Probablemente ya tenga un script "crear esquema". Reutilizarlo para el aislamiento de prueba es fácil.
2. Crear un esquema es bastante rápido.
3. Con ese enfoque, es muy fácil configurar su script para que cada dispositivo cree un NUEVO esquema (con un nombre temporal), y luego puede comenzar a ejecutar los dispositivos de prueba en paralelo, haciendo que la parte más lenta de su conjunto de pruebas sea mucho más rápida .

Answer 11

Sin ALTER TABLE

-- Delete all records
DELETE FROM [TableName]
-- Set current ID to "1"
-- If table already contains data, use "0"
-- If table is empty and never insert data, use "1"
-- Use SP https://github.com/reduardo7/TableTruncate
DBCC CHECKIDENT ([TableName], RESEED, [0|1])

Como procedimiento almacenado

https://github.com/reduardo7/TableTruncate

Tenga en cuenta que esto no es probablemente lo que desearía si tuviera millones de registros, ya que es muy lento.

Answer 12

Truncar no funcionó para mí, eliminar + reiniciar es la mejor manera de salir. En caso de que haya algunos de ustedes que necesiten iterar sobre un gran número de tablas para realizar borrar + reiniciar, es posible que tenga problemas con algunas tablas que no tienen una columna de identidad, el siguiente código verifica si existe una columna de identidad antes de intentar para volver a sembrar

    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

Answer 13

puede seguir este paso. Al reseeding table , puede eliminar los datos de la tabla.

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

Si se produce algún error, deberá reiniciar la tabla principal.

Answer 14

use el siguiente comando después de eliminar todas las filas en esa tabla usando la declaración de eliminación

delete from tablename

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

EDITAR: sintaxis corregida para SQL Server

Answer 15

Si ninguna de estas respuestas funcionó como en mi caso, haga esto:

1. Restricciones de caída
2. Establecer todos los valores para permitir nulos
3. Mesa truncada
4. Añadir restricciones que fueron eliminadas.

¡Buena suerte!

Answer 16

Tu podrías intentar DELETE FROM <your table >; .

El servidor le mostrará el nombre de la restricción y la tabla, y al eliminar esa tabla puede eliminar lo que necesite.

Answer 17

Acabo de descubrir que puede usar la tabla TRUNCATE en una tabla principal con restricciones de clave externa en un elemento secundario siempre que DESACTIVE las restricciones en la tabla secundaria primero. P.ej

La clave externa CONSTRAINT child_par_ref en la tabla secundaria, hace referencia a 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;

Answer 18

En SSMS tuve Diagrama abierto mostrando la clave. Después de eliminar la Clave y truncar el archivo, actualicé, luego volví a concentrarme en el Diagrama y creamos una actualización, borrando y luego restaurando un cuadro de Identidad. Al guardar el Diagrama apareció un cuadro de diálogo Guardar, que un cuadro de diálogo "Se hicieron cambios en la base de datos mientras estaba trabajando", al hacer clic en Sí restauró la Clave, restaurándola desde la copia enclavada en el Diagrama.

Answer 19

Escribo las siguientes formas y traté de parametrizarlas, para que puedas ejecutarlas en Query documento hacerlas útiles SPfácilmente .

A) Eliminar

Si su tabla no tiene millones de registros, esto funciona bien y no tiene ningún comando 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)

• En mi respuesta anterior, el método para resolver el problema mencionado en la pregunta se basa en la respuesta @ s15199d .

B) Truncar

Si su tabla tiene millones de registros o no tiene ningún problema con el comando Alter en sus códigos, entonces use este:

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

• En mi respuesta anterior, el método para resolver el problema mencionado en la pregunta se basa en la answer@LauroWolffValenteSobrinho .answer

• Si tiene más de un CONSTRAINT, debe adjuntar sus códigos como yo a la consulta anterior.

• También puede cambiar la base de código anterior @SerjSagan answer para deshabilitar y habilitar la restricción

Answer 20

La forma más fácil:
1 - Ingrese en phpmyadmin
2 - Haga clic en el nombre de la tabla en la columna izquierda
3 - Haga clic en Operación (menú superior)
4 - Haga clic en "Vaciar la tabla (TRUNCATE)
5 - Desactive la casilla" Habilitar verificación de clave foránea "
6 - Listo !

Enlace al tutorial de imágenes
Tutorial: http://www.imageno.com/wz6gv1wuqajrpic.html
(lo siento, no tengo suficiente reputación para subir imágenes aquí: P)

Answer 21

Para MS SQL, al menos las versiones más nuevas, puedes deshabilitar las restricciones con un código como este:

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

Answer 22

Si está haciendo esto en cualquier tipo de frecuencia, incluso en un horario, absolutamente, de forma inequívoca, nunca usaría una declaración DML. El costo de escribir en el registro de transacciones es demasiado alto, y configurar toda la base de datos en SIMPLEmodo de recuperación para truncar una tabla es ridículo.

La mejor manera, es desafortunadamente la forma dura o laboriosa. Ese ser:

• Restricciones de caída
• Mesa truncada
• Volver a crear restricciones

Mi proceso para hacer esto implica los siguientes pasos:

1. En SSMS, haga clic con el botón derecho en la tabla en cuestión y seleccione Ver dependencias
2. Tome nota de las tablas a las que se hace referencia (si las hay)
3. De vuelta en el explorador de objetos, expanda el nodo Claves y tome nota de las claves externas (si las hay)
4. Iniciar secuencias de comandos (soltar / truncar / volver a crear)

Guiones de esta naturaleza deben hacerse dentro de una begin trany de commit tranbloque.

Answer 23

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

Tenga en cuenta que esto no es probablemente lo que desearía si tuviera millones de registros, ya que es muy lento.

Answer 24

SET FOREIGN_KEY_CHECKS = 0; 

truncate table "yourTableName";

SET FOREIGN_KEY_CHECKS = 1;

Answer 25

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

referencia - truncar la tabla restringida de clave externa

Trabajando para mi en MYSQL