MSSQL2005를 사용하여 자식 테이블 (FK 관계의 기본 키가있는 테이블)을 먼저 자르면 외래 키 제약 조건으로 테이블을자를 수 있습니까?

나도 할 수 있다는 것을 안다

DELETEwhere 절을 사용 하지 않고 RESEEDidentity를 사용하십시오.
FK를 제거하고 테이블을 자르고 FK를 다시 작성하십시오.

부모 앞에서 자식 테이블을 자르면 위의 옵션 중 하나를 수행하지 않아도 괜찮을 것이라고 생각했지만이 오류가 발생합니다.

테이블 ‘TableName’이 FOREIGN KEY 제약 조건에 의해 참조되므로 잘라낼 수 없습니다.

답변

옳은; FK 제약 조건이있는 테이블은자를 수 없습니다.

일반적으로 내 프로세스는 다음과 같습니다.

구속 조건 삭제
테이블 자르기
구속 조건을 다시 작성하십시오.

(물론 거래에서 모두.)

물론 이것은 자식이 이미 잘린 경우에만 적용됩니다 . 그렇지 않으면 나는 내 데이터의 모양에 전적으로 의존하여 다른 경로를 간다. (여기에 들어갈 변수가 너무 많습니다.)

원래의 포스터가 왜 그런지 결정했습니다. 자세한 내용 은 이 답변 을 참조하십시오.

답변

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

매우 느리기 때문에 수백만 개 이상의 레코드를 보유하고 있다면 이것이 원하는 것은 아닙니다.

답변

때문에 TRUNCATE TABLEA는 DDL 명령 , 그것은 테이블의 레코드가 자식 테이블의 레코드에 의해 참조되고 있는지 여부를 확인할 수 없습니다.

데이터베이스가 다른 레코드에 의해 참조되고 있지 않은지 확인할 수 있기 때문에 이것이 DELETE작동하고 작동 TRUNCATE TABLE하지 않는 이유 입니다.

답변

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

저장 프로 시저

https://github.com/reduardo7/TableTruncate

참고 이것은 당신이 + 기록의 수백만이있는 경우가 매우 느린 당신이 원하는 것 무엇을 아마입니다.

답변

위에 제공된 @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

답변

delete 문을 사용하여 해당 테이블의 모든 행을 삭제 한 후 다음 명령을 사용하십시오.

delete from tablename

DBCC CHECKIDENT ('tablename', RESEED, 0)

편집 : SQL Server의 구문 수정

답변

글쎄, 내가 사용한 매우 간단한 솔루션의 예 를 찾지 못했기 때문에 다음과 같습니다.

외래 키를 삭제하십시오.
테이블 자르기
외래 키 재 작성

여기 간다:

1) 실패의 원인이되는 외래 키 이름을 찾으십시오 (예 : ID테이블에서 필드가있는 FK_PROBLEM_REASON 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)

그게 다야.