source

모든 외부 키 제약 조건 일시적으로 사용 안 함

lovecheck 2023. 7. 1. 08:58
반응형

모든 외부 키 제약 조건 일시적으로 사용 안 함

몇 개 테이블의 데이터를 플랫 파일에서 데이터베이스의 기존 테이블로 대체하는 SSIS 패키지를 실행하고 있습니다.

내 패키지가 테이블을 잘라낸 다음 새 데이터를 삽입합니다.SSIS 패키지를 실행할 때 외부 키 때문에 예외가 발생합니다.

제약 조건을 비활성화하고 가져오기를 실행한 다음 다시 활성화할 수 있습니까?

외부 키 제약 조건을 비활성화하려면:

DECLARE @sql nvarchar(max) = N'';

;WITH x AS 
(
  SELECT DISTINCT obj = 
      QUOTENAME(OBJECT_SCHEMA_NAME(parent_object_id)) + '.' 
    + QUOTENAME(OBJECT_NAME(parent_object_id)) 
  FROM sys.foreign_keys
)
SELECT @sql += N'ALTER TABLE ' + obj + N' NOCHECK CONSTRAINT ALL;
' FROM x;

EXEC sys.sp_executesql @sql;

다시 활성화하기

DECLARE @sql nvarchar(max) = N'';

;WITH x AS 
(
  SELECT DISTINCT obj = 
      QUOTENAME(OBJECT_SCHEMA_NAME(parent_object_id)) + '.' 
    + QUOTENAME(OBJECT_NAME(parent_object_id)) 
  FROM sys.foreign_keys
)
SELECT @sql += N'ALTER TABLE ' + obj + N' WITH CHECK CHECK CONSTRAINT ALL;
' FROM x;

EXEC sys.sp_executesql @sql;

그러나 테이블을 잘라낼 수 없으므로 올바른 순서로 테이블에서 삭제해야 합니다.이러한 제약 조건을 잘라내야 하는 경우 제약 조건을 완전히 삭제하고 다시 만들어야 합니다.외부 키 제약 조건이 모두 단순한 단일 열 제약 조건인 경우 이 작업은 간단하지만 여러 열이 관련된 경우에는 더 복잡합니다.

여기 당신이 시도할 수 있는 것이 있습니다.SSIS 패키지의 일부로 만들려면 SSIS 패키지가 실행되는 동안 FK 정의를 저장할 공간이 필요합니다(하나의 스크립트에서 이 모든 작업을 수행할 수 없음).따라서 일부 유틸리티 데이터베이스에서 테이블을 만듭니다.

CREATE TABLE dbo.PostCommand(cmd nvarchar(max));

그런 다음 데이터베이스에서 다음 작업을 수행하는 저장 프로시저를 사용할 수 있습니다.

DELETE other_database.dbo.PostCommand;

DECLARE @sql nvarchar(max) = N'';

SELECT @sql += N'ALTER TABLE ' 
   + QUOTENAME(OBJECT_SCHEMA_NAME(fk.parent_object_id))
   + '.' + QUOTENAME(OBJECT_NAME(fk.parent_object_id)) 
   + ' ADD CONSTRAINT ' + fk.name + ' FOREIGN KEY (' 
   + STUFF((SELECT ',' + c.name
    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(''), 
    TYPE).value(N'./text()[1]', 'nvarchar(max)'), 1, 1, N'')
+ ') REFERENCES ' + 
QUOTENAME(OBJECT_SCHEMA_NAME(fk.referenced_object_id))
+ '.' + QUOTENAME(OBJECT_NAME(fk.referenced_object_id))
+ '(' + 
STUFF((SELECT ',' + c.name
    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(''), 
    TYPE).value(N'./text()[1]', N'nvarchar(max)'), 1, 1, N'') + ');
' FROM sys.foreign_keys AS fk
WHERE OBJECTPROPERTY(parent_object_id, 'IsMsShipped') = 0;

INSERT other_database.dbo.PostCommand(cmd) SELECT @sql;

IF @@ROWCOUNT = 1
BEGIN
  SET @sql = N'';

  SELECT @sql += N'ALTER TABLE ' + QUOTENAME(OBJECT_SCHEMA_NAME(fk.parent_object_id))
    + '.' + QUOTENAME(OBJECT_NAME(fk.parent_object_id)) 
    + ' DROP CONSTRAINT ' + fk.name + ';
  ' FROM sys.foreign_keys AS fk;

  EXEC sys.sp_executesql @sql;
END

이제 SSIS 패키지가 완료되면 다른 저장 프로시저를 호출해야 합니다. 그러면 다음 작업이 수행됩니다.

DECLARE @sql nvarchar(max);

SELECT @sql = cmd FROM other_database.dbo.PostCommand;

EXEC sys.sp_executesql @sql;

만약 당신이 이 모든 것을 삭제하는 대신 잘라낼 수 있기 위해서라면, 그냥 히트를 치고 삭제를 실행하는 것을 제안합니다.대량으로 기록된 복구 모델을 사용하여 로그의 영향을 최소화할 수 있습니다.일반적으로 이 솔루션이 올바른 순서로 삭제를 사용하는 것보다 훨씬 빠를 것이라고는 생각하지 않습니다.

2014년에 저는 여기에 이에 대한 더 정교한 게시물을 게시했습니다.

각 테이블 저장 프로시저에 내장된 sp_ms를 사용합니다.

모든 제약 조건을 비활성화하려면:

EXEC sp_msforeachtable "ALTER TABLE ? NOCHECK CONSTRAINT ALL";

모든 제약 조건을 활성화하려면:

EXEC sp_msforeachtable "ALTER TABLE ? WITH CHECK CHECK CONSTRAINT ALL";

모든 테이블을 삭제하는 방법

EXEC sp_msforeachtable "DROP TABLE ?";

이것을 위한 쉬운 방법이 있습니다.

-- Disable all the constraint in database
EXEC sp_msforeachtable 'ALTER TABLE ? NOCHECK CONSTRAINT all'

-- Enable all the constraint in database
EXEC sp_msforeachtable 'ALTER TABLE ? WITH CHECK CHECK CONSTRAINT all'

참조 SQL SERVER – 데이터베이스에서 모든 외부 키 제약 조건 사용 안 함 – 데이터베이스에서 모든 외부 키 제약 조건 사용

http://msdn.microsoft.com/en-us/magazine/cc163442.aspx 에서 "모든 외부 키 사용 안 함" 섹션을 참조하십시오.

여기서 영감을 받아 임시 테이블을 만들고 해당 테이블에 제약 조건을 삽입한 다음 제약 조건을 삭제한 다음 해당 임시 테이블에서 다시 적용하여 접근할 수 있습니다.여기서 충분히 말한 것이 내가 말하고 있는 것입니다.

 SET NOCOUNT ON

    DECLARE @temptable TABLE(
       Id INT PRIMARY KEY IDENTITY(1, 1),
       FKConstraintName VARCHAR(255),
       FKConstraintTableSchema VARCHAR(255),
       FKConstraintTableName VARCHAR(255),
       FKConstraintColumnName VARCHAR(255),
       PKConstraintName VARCHAR(255),
       PKConstraintTableSchema VARCHAR(255),
       PKConstraintTableName VARCHAR(255),
       PKConstraintColumnName VARCHAR(255)    
    )

    INSERT INTO @temptable(FKConstraintName, FKConstraintTableSchema, FKConstraintTableName, FKConstraintColumnName)
    SELECT 
       KeyColumnUsage.CONSTRAINT_NAME, 
       KeyColumnUsage.TABLE_SCHEMA, 
       KeyColumnUsage.TABLE_NAME, 
       KeyColumnUsage.COLUMN_NAME 
    FROM 
       INFORMATION_SCHEMA.KEY_COLUMN_USAGE KeyColumnUsage
          INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS TableConstraints
             ON KeyColumnUsage.CONSTRAINT_NAME = TableConstraints.CONSTRAINT_NAME
    WHERE
       TableConstraints.CONSTRAINT_TYPE = 'FOREIGN KEY'

    UPDATE @temptable SET
       PKConstraintName = UNIQUE_CONSTRAINT_NAME
    FROM 
       @temptable tt
          INNER JOIN INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS ReferentialConstraint
             ON tt.FKConstraintName = ReferentialConstraint.CONSTRAINT_NAME

    UPDATE @temptable SET
       PKConstraintTableSchema  = TABLE_SCHEMA,
       PKConstraintTableName  = TABLE_NAME
    FROM @temptable tt
       INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS TableConstraints
          ON tt.PKConstraintName = TableConstraints.CONSTRAINT_NAME

    UPDATE @temptable SET
       PKConstraintColumnName = COLUMN_NAME
    FROM @temptable tt
       INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE KeyColumnUsage
          ON tt.PKConstraintName = KeyColumnUsage.CONSTRAINT_NAME


    --Now to drop constraint:
    SELECT
       '
       ALTER TABLE [' + FKConstraintTableSchema + '].[' + FKConstraintTableName + '] 
       DROP CONSTRAINT ' + FKConstraintName + '

       GO'
    FROM
       @temptable

    --Finally to add constraint:
    SELECT
       '
       ALTER TABLE [' + FKConstraintTableSchema + '].[' + FKConstraintTableName + '] 
       ADD CONSTRAINT ' + FKConstraintName + ' FOREIGN KEY(' + FKConstraintColumnName + ') REFERENCES [' + PKConstraintTableSchema + '].[' + PKConstraintTableName + '](' + PKConstraintColumnName + ')

       GO'
    FROM
       @temptable

    GO

모든 테이블 제약 조건 사용 안 함

ALTER TABLE TableName NOCHECK CONSTRAINT ConstraintName

모든 테이블 제약 조건 사용

ALTER TABLE TableName CHECK CONSTRAINT ConstraintName

".dbo"와 다른 데이터베이스 스키마를 사용하거나 여러 필드로 구성된 Pk를 DB에 포함하는 경우 Carter Medlin 솔루션을 사용하지 마십시오. 그렇지 않으면 DB가 손상됩니다!

다른 스키마로 작업할 때는 이 작업을 수행하십시오(이전에 데이터베이스를 백업하는 것을 잊지 마십시오!).

DECLARE @sql AS NVARCHAR(max)=''
select @sql = @sql +
    'ALTER INDEX ALL ON ' + SCHEMA_NAME( t.schema_id) +'.'+ '['+ t.[name] + '] DISABLE;'+CHAR(13)
from  
    sys.tables t
where type='u'

select @sql = @sql +
    'ALTER INDEX ' + i.[name] + ' ON ' + SCHEMA_NAME( t.schema_id) +'.'+'[' + t.[name] + '] REBUILD;'+CHAR(13)
from  
    sys.key_constraints i
join
    sys.tables t on i.parent_object_id=t.object_id
where     i.type='PK'

exec dbo.sp_executesql @sql;
go

몇 가지 FK 프리 작업을 수행한 후 다음으로 다시 전환할 수 있습니다.

DECLARE @sql AS NVARCHAR(max)=''
select @sql = @sql +
    'ALTER INDEX ALL ON ' + SCHEMA_NAME( t.schema_id) +'.'+'[' +  t.[name] + '] REBUILD;'+CHAR(13)
from  
    sys.tables t
where type='u'
print @sql

exec dbo.sp_executesql @sql;
exec sp_msforeachtable "ALTER TABLE ? WITH NOCHECK CHECK CONSTRAINT ALL";

sql에서 사이드 가능한 FK에 대한 쿼리를 실행할 필요가 없습니다.표 A에서 B까지 FK가 있는 경우 다음을 수행해야 합니다.

  • 테이블 A에서 데이터 삭제
  • 테이블 B에서 데이터 삭제
  • B에 대한 데이터 삽입
  • A에 대한 데이터 삽입

대상에게 제약 조건을 확인하지 않도록 할 수도 있습니다.

enter image description here

외부 키를 사용하지 않도록 설정한 경우에도 테이블을 자를 수 없습니다.따라서 delete 명령을 사용하여 테이블에서 모든 레코드를 제거할 수 있지만 수백만 개의 레코드로 구성된 테이블에 대해 delete 명령을 사용하는 경우 패키지 속도가 느려지고 트랜잭션 로그 크기가 증가하여 귀중한 디스크 공간을 채울 수 있습니다.

제약 조건을 삭제하면 테이블에 부정한 데이터가 가득 차게 되고, 제약 조건을 다시 작성하려고 하면 오류가 발생하기 때문에 이를 허용하지 않을 수 있습니다. 따라서 제약 조건을 삭제하면 다음과 같이 하십시오.서로 정확하게 관련되어 있고 다시 생성할 제약 조건 관계를 충족하는 데이터를 로드하고 있습니다.

따라서 각 방법의 장단점을 신중하게 생각하고 요구 사항에 따라 사용하십시오.

모든 인덱스(모든 fks를 사용하지 않도록 설정하는 pk 포함)를 사용하지 않도록 설정한 다음 pks를 다시 사용하도록 설정합니다.

DECLARE @sql AS NVARCHAR(max)=''
select @sql = @sql +
    'ALTER INDEX ALL ON [' + t.[name] + '] DISABLE;'+CHAR(13)
from  
    sys.tables t
where type='u'

select @sql = @sql +
    'ALTER INDEX ' + i.[name] + ' ON [' + t.[name] + '] REBUILD;'+CHAR(13)
from  
    sys.key_constraints i
join
    sys.tables t on i.parent_object_id=t.object_id
where
    i.type='PK'


exec dbo.sp_executesql @sql;
go

[데이터 로드]

그리고 모든 것을 되살리세요...

DECLARE @sql AS NVARCHAR(max)=''
select @sql = @sql +
    'ALTER INDEX ALL ON [' + t.[name] + '] REBUILD;'+CHAR(13)
from  
    sys.tables t
where type='u'

exec dbo.sp_executesql @sql;
go

언급URL : https://stackoverflow.com/questions/11639868/temporarily-disable-all-foreign-key-constraints

반응형

'source' 카테고리의 다른 글

mat-select에 대한 다중 값 사전 선택  (0) 2023.07.01
Xcode 10에서 오브젝트 라이브러리는 어디로 갔습니까?  (0) 2023.07.01
설치된 모든 Gem을 제거하고 다시 시작하는 중  (0) 2023.07.01
안드로이드 스튜디오에서 그라들을 업데이트하는 방법은 무엇입니까?  (0) 2023.07.01
angular2 및 typescript에서 배열을 초기화하는 방법  (0) 2023.06.26

'source'의 다른글

  • 현재글모든 외부 키 제약 조건 일시적으로 사용 안 함

관련글

댓글

티스토리툴바