반응형
T-SQL을 사용하여 외부 키 제약을 일시적으로 해제하려면 어떻게 해야 합니까?
SQL Server에서 외부 키 제약 조건 비활성화 및 활성화가 지원됩니까?아니면 제약 조건을 다시 적용하는create 방법밖에 없나요?
데이터베이스의 모든 제약을 비활성화하려면 다음 코드를 실행하십시오.
-- disable all constraints
EXEC sp_MSforeachtable "ALTER TABLE ? NOCHECK CONSTRAINT all"
스위치를 다시 켜려면 다음을 수행합니다(물론 인쇄는 옵션이며 표만 나열됩니다).
-- enable all constraints
exec sp_MSforeachtable @command1="print '?'", @command2="ALTER TABLE ? WITH CHECK CHECK CONSTRAINT all"
한 데이터베이스에서 다른 데이터베이스로 데이터를 채울 때 유용합니다.그것은 제약을 없애는 것보다 훨씬 더 나은 접근법이다.이미 언급했듯이 데이터베이스의 모든 데이터를 삭제하고 다시 채울 때(테스트 환경에서 등) 편리합니다.
모든 데이터를 삭제할 경우 이 솔루션이 도움이 될 수 있습니다.
또, 모든 트리거를 무효로 하는 것도 편리할 수 있습니다.여기서 완전한 솔루션을 보실 수 있습니다.
(현재 Wayback Machine에 보관되어 있는 http://www.sqljunkies.com/WebLog/roman/archive/2005/01/30/7037.aspx,에서 복사)
외부 키 제약 및 검사 제약은 데이터 무결성 및 비즈니스 규칙을 적용하는 데 매우 유용합니다.다만, 이러한 동작은 불필요하거나 득보다 실이 많을 수 있기 때문에 일시적으로 전원을 끄는 것이 도움이 되는 시나리오가 있습니다.외부 소스로부터의 데이터 로드 중 또는 데이터를 테이블로 다시 로드하여 테이블 폐기/재호출 스크립트를 작성할 때 테이블 제약 체크를 비활성화할 수 있습니다.보통 수백만 개의 행 중 하나 또는 몇 개의 행에 잘못된 데이터가 포함되어 있기 때문에 시간이 많이 걸리는 프로세스가 실패하는 것을 원치 않는 시나리오에서 이 작업을 수행합니다.그러나 프로세스가 완료되면 항상 제약 조건을 다시 설정하고 경우에 따라 가져온 데이터에 대해 데이터 무결성 검사를 실행합니다.
외부 키 제약 조건을 디세블로 하면 부모 테이블에 존재하지 않는 값을 삽입할 수 있습니다.체크 제약조건을 디세블로 하면 체크 제약조건이 존재하지 않는 것처럼 컬럼에 값을 넣을 수 있습니다.다음으로 테이블 구속조건을 디세이블 및 이니블로 하는 예를 몇 가지 나타냅니다.
-- Disable all table constraints ALTER TABLE MyTable NOCHECK CONSTRAINT ALL -- Enable all table constraints ALTER TABLE MyTable WITH CHECK CHECK CONSTRAINT ALL -- Disable single constraint ALTER TABLE MyTable NOCHECK CONSTRAINT MyConstraint -- Enable single constraint ALTER TABLE MyTable WITH CHECK CHECK CONSTRAINT MyConstraint
, 「」를 사용합니다.ALTERNOCHECK를 사용한 테이블
ALTER TABLE [TABLE_NAME] NOCHECK CONSTRAINT [ALL|CONSTRAINT_NAME]
더블 체크를 사용할 수 있도록 하려면:
ALTER TABLE [TABLE_NAME] WITH CHECK CHECK CONSTRAINT [ALL|CONSTRAINT_NAME]
- 활성화 시 Double CHECK(더블 체크)에 주의하십시오.
- ALL은 표의 모든 구속조건에 대한 평균입니다.
완료 후 상태를 확인해야 할 경우 이 스크립트를 사용하여 제약 조건 상태를 나열합니다.매우 도움이 됩니다.
SELECT (CASE
WHEN OBJECTPROPERTY(CONSTID, 'CNSTISDISABLED') = 0 THEN 'ENABLED'
ELSE 'DISABLED'
END) AS STATUS,
OBJECT_NAME(CONSTID) AS CONSTRAINT_NAME,
OBJECT_NAME(FKEYID) AS TABLE_NAME,
COL_NAME(FKEYID, FKEY) AS COLUMN_NAME,
OBJECT_NAME(RKEYID) AS REFERENCED_TABLE_NAME,
COL_NAME(RKEYID, RKEY) AS REFERENCED_COLUMN_NAME
FROM SYSFOREIGNKEYS
ORDER BY TABLE_NAME, CONSTRAINT_NAME,REFERENCED_TABLE_NAME, KEYNO
가장 좋은 옵션은 외부 키 제약 조건을 드롭하고 작성하는 것입니다.
이 게시물에서는 "있는 그대로" 사용할 수 있는 예를 찾을 수 없었습니다. 하나는 외부 키가 다른 스키마를 참조하는 경우 작동하지 않으며 다른 하나는 외부 키가 여러 열을 참조하는 경우 작동하지 않습니다.이 스크립트에서는 외부 키별로 여러 스키마와 여러 열을 모두 고려합니다.
다음은 "ADD CONSTRAREN" 문을 생성하는 스크립트입니다. 여러 열에 대해 쉼표로 구분합니다(DROP 문을 실행하기 전에 다음 출력을 저장하십시오).
PRINT N'-- CREATE FOREIGN KEY CONSTRAINTS --';
SET NOCOUNT ON;
SELECT '
PRINT N''Creating '+ const.const_name +'...''
GO
ALTER TABLE ' + const.parent_obj + '
ADD CONSTRAINT ' + const.const_name + ' FOREIGN KEY (
' + const.parent_col_csv + '
) REFERENCES ' + const.ref_obj + '(' + const.ref_col_csv + ')
GO'
FROM (
SELECT QUOTENAME(fk.NAME) AS [const_name]
,QUOTENAME(schParent.NAME) + '.' + QUOTENAME(OBJECT_name(fkc.parent_object_id)) AS [parent_obj]
,STUFF((
SELECT ',' + QUOTENAME(COL_NAME(fcP.parent_object_id, fcp.parent_column_id))
FROM sys.foreign_key_columns AS fcP
WHERE fcp.constraint_object_id = fk.object_id
FOR XML path('')
), 1, 1, '') AS [parent_col_csv]
,QUOTENAME(schRef.NAME) + '.' + QUOTENAME(OBJECT_NAME(fkc.referenced_object_id)) AS [ref_obj]
,STUFF((
SELECT ',' + QUOTENAME(COL_NAME(fcR.referenced_object_id, fcR.referenced_column_id))
FROM sys.foreign_key_columns AS fcR
WHERE fcR.constraint_object_id = fk.object_id
FOR XML path('')
), 1, 1, '') AS [ref_col_csv]
FROM sys.foreign_key_columns AS fkc
INNER JOIN sys.foreign_keys AS fk ON fk.object_id = fkc.constraint_object_id
INNER JOIN sys.objects AS oParent ON oParent.object_id = fkc.parent_object_id
INNER JOIN sys.schemas AS schParent ON schParent.schema_id = oParent.schema_id
INNER JOIN sys.objects AS oRef ON oRef.object_id = fkc.referenced_object_id
INNER JOIN sys.schemas AS schRef ON schRef.schema_id = oRef.schema_id
GROUP BY fkc.parent_object_id
,fkc.referenced_object_id
,fk.NAME
,fk.object_id
,schParent.NAME
,schRef.NAME
) AS const
ORDER BY const.const_name
다음은 "DROP RESTRAING" 문을 생성하는 스크립트입니다.
PRINT N'-- DROP FOREIGN KEY CONSTRAINTS --';
SET NOCOUNT ON;
SELECT '
PRINT N''Dropping ' + fk.NAME + '...''
GO
ALTER TABLE [' + sch.NAME + '].[' + OBJECT_NAME(fk.parent_object_id) + ']' + ' DROP CONSTRAINT ' + '[' + fk.NAME + ']
GO'
FROM sys.foreign_keys AS fk
INNER JOIN sys.schemas AS sch ON sch.schema_id = fk.schema_id
ORDER BY fk.NAME
SQL-92 표준에서는 DEFERRABLE로 선언하여 트랜잭션 범위 내에서 (암시적 또는 명시적으로) 연기할 수 있도록 합니다.안타깝게도 SQL Server에는 아직 이 SQL-92 기능이 없습니다.
나에게 있어 제약조건을 NOCHECK로 변경하는 것은 데이터베이스 구조를 즉석에서 변경하는 것과 유사하며, (예를 들어, 사용자는 더 많은 권한을 필요로 한다) 피해야 할 것이다.
--Drop and Recreate Foreign Key Constraints
SET NOCOUNT ON
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
--SELECT * FROM @table
--DROP CONSTRAINT:
SELECT
'
ALTER TABLE [' + ForeignKeyConstraintTableSchema + '].[' + ForeignKeyConstraintTableName + ']
DROP CONSTRAINT ' + ForeignKeyConstraintName + '
GO'
FROM
@table
--ADD CONSTRAINT:
SELECT
'
ALTER TABLE [' + ForeignKeyConstraintTableSchema + '].[' + ForeignKeyConstraintTableName + ']
ADD CONSTRAINT ' + ForeignKeyConstraintName + ' FOREIGN KEY(' + ForeignKeyConstraintColumnName + ') REFERENCES [' + PrimaryKeyConstraintTableSchema + '].[' + PrimaryKeyConstraintTableName + '](' + PrimaryKeyConstraintColumnName + ')
GO'
FROM
@table
GO
네 말에 동의해, 햄린SSIS를 사용하여 데이터를 전송하는 경우 또는 데이터를 복제하는 경우 외부 키 제약 조건을 일시적으로 비활성화하거나 삭제한 후 다시 활성화하거나 다시 생성해야 합니다.이 경우 참조 무결성은 소스 데이터베이스에 이미 유지되므로 문제가 되지 않습니다.따라서 안심하셔도 됩니다.
WITH CHECK CHECK거의 확실히 필요합니다!
이 점은 일부 답변과 댓글에서 제기되었지만 다시 한번 언급하는 것이 중요하다고 생각합니다.
을 사용하여 을 다시 (no ada 음 re re reno （ no ）WITH CHECK)에는 몇 가지 중대한 단점이 있습니다.
ALTER TABLE MyTable CHECK CONSTRAINT MyConstraint;
체크 포함 | 체크 없음
테이블의 데이터가 새로 추가되거나 다시 활성화된 FORENAL KEY 또는 CHECK 제약 조건에 대해 검증되는지 여부를 지정합니다.지정되지 않은 경우 WITH CHECK은 새로운 제약조건에 대해 가정하고 WITH NOCHECK은 다시 활성화된 제약조건에 대해 가정합니다.
기존 데이터에 대해 새로운 CHECK 또는 FORNE KEY 제약을 검증하지 않으려면 WITH NOCHECK와 함께 사용하십시오.드문 경우를 제외하고는 이 작업을 수행하지 않는 것이 좋습니다.새로운 제약 조건은 이후의 모든 데이터 업데이트에서 평가됩니다.제약 조건이 추가될 때 WITH NOCHECK에 의해 억제되는 제약 조건 위반은 제약 조건을 준수하지 않는 데이터로 행을 업데이트하면 향후 업데이트가 실패할 수 있습니다.
쿼리 옵티마이저는 NOCHECK에서 정의된 제약 조건을 고려하지 않습니다.이러한 제약 조건은 CHECK CHECK RESTRACT ALL과 함께 ALTER TABLE 테이블을 사용하여 다시 활성화될 때까지 무시됩니다.
주의: WITH NOCHECK는 제약 조건을 다시 활성화하는 기본값입니다.왜 그런지 궁금하군요
- 이 명령어를 실행하는 동안 테이블 내의 기존 데이터는 평가되지 않습니다.완료되었다고 해서 테이블 내의 데이터가 제약조건에 따라 유효하다는 보장은 없습니다.
- 비활성 레코드의 다음 업데이트 시 제약 조건이 평가되고 오류가 발생하며 실제 업데이트와 관련이 없는 오류가 발생할 수 있습니다.
- 데이터가 유효한지 확인하기 위해 제약 조건에 의존하는 응용 프로그램 로직이 실패할 수 있습니다.
- 쿼리 옵티마이저는 이러한 방식으로 활성화된 제약 조건을 사용하지 않습니다.
시스템.foreign_keys 시스템 뷰에서는 문제를 확인할 수 있습니다.두 가지 기능을 모두 갖추고 있다는 점에 주의해 주세요.is_disabled ★★★is_not_trusted★★★★★★ 。is_disabled는, 조작 이, 제약 에 . is_not_trusted는 현재 테이블 내의 모든 데이터가 제약조건에 대해 검증되었는지 여부를 나타냅니다.
ALTER TABLE MyTable WITH CHECK CHECK CONSTRAINT MyConstraint;
당신의 제약은 신뢰할 수 있습니까?검색...
SELECT * FROM sys.foreign_keys WHERE is_not_trusted = 1;
SET NOCOUNT ON
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),
UpdateRule NVARCHAR(100),
DeleteRule NVARCHAR(100)
)
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
T.PrimaryKeyConstraintName = R.UNIQUE_CONSTRAINT_NAME,
T.UpdateRule = R.UPDATE_RULE,
T.DeleteRule = R.DELETE_RULE
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
--SELECT * FROM @table
SELECT '
BEGIN TRANSACTION
BEGIN TRY'
--DROP CONSTRAINT:
SELECT
'
ALTER TABLE [' + ForeignKeyConstraintTableSchema + '].[' + ForeignKeyConstraintTableName + ']
DROP CONSTRAINT ' + ForeignKeyConstraintName + '
'
FROM
@table
SELECT '
END TRY
BEGIN CATCH
ROLLBACK TRANSACTION
RAISERROR(''Operation failed.'', 16, 1)
END CATCH
IF(@@TRANCOUNT != 0)
BEGIN
COMMIT TRANSACTION
RAISERROR(''Operation completed successfully.'', 10, 1)
END
'
--ADD CONSTRAINT:
SELECT '
BEGIN TRANSACTION
BEGIN TRY'
SELECT
'
ALTER TABLE [' + ForeignKeyConstraintTableSchema + '].[' + ForeignKeyConstraintTableName + ']
ADD CONSTRAINT ' + ForeignKeyConstraintName + ' FOREIGN KEY(' + ForeignKeyConstraintColumnName + ') REFERENCES [' + PrimaryKeyConstraintTableSchema + '].[' + PrimaryKeyConstraintTableName + '](' + PrimaryKeyConstraintColumnName + ') ON UPDATE ' + UpdateRule + ' ON DELETE ' + DeleteRule + '
'
FROM
@table
SELECT '
END TRY
BEGIN CATCH
ROLLBACK TRANSACTION
RAISERROR(''Operation failed.'', 16, 1)
END CATCH
IF(@@TRANCOUNT != 0)
BEGIN
COMMIT TRANSACTION
RAISERROR(''Operation completed successfully.'', 10, 1)
END'
GO
첫 번째 투고 :)
OP의 경우 대량 데이터 및 대량 삭제 시 트랜잭션 로그 풍선 문제가 발생하지 않는 한 Kristof의 솔루션이 작동합니다.또한 tlog 저장 공간을 확보하더라도 tlog에 대한 쓰기를 삭제하기 때문에 수억 개의 행이 있는 테이블에서는 작업이 매우 오래 걸릴 수 있습니다.
저는 일련의 커서를 사용하여 대규모 프로덕션 데이터베이스 중 하나의 대용량 복사본을 자주 잘라내고 새로고침합니다.솔루션 엔지니어링은 여러 스키마, 여러 외부 키 열, 그리고 무엇보다도 SSIS에서 사용하기 위해 sproc'd를 사용할 수 있습니다.
여기에는 DROP, CREATE 및 CHECK FK 스크립트를 수용하기 위한 3개의 스테이징 테이블(실제 테이블)의 작성과 테이블로의 스크립트의 작성과 삽입, 테이블상의 루프와 실행이 포함됩니다.첨부된 스크립트는 4개의 부분으로 구성되어 있습니다. 1. 3개의 스테이징(실제) 테이블 내의 스크립트의 작성과 저장 2. 커서로1개씩 드롭된 FK 스크립트의 실행 3. sp_MSoreachable을 사용하여 3개의 스테이징 테이블과 4 이외의 모든 테이블을 잘라냅니다.) 작성 FK 실행 및 ETL SSIS 패키지의 마지막에 FK 스크립트를 체크합니다.
SSIS의 SQL 실행 태스크에서 스크립트 작성 부분을 실행합니다.두 번째 SQL 실행 태스크에서 "FK 스크립트 삭제" 부분을 실행합니다.세 번째 SQL 실행 태스크에 트랜케이션스크립트를 넣은 후 제어 흐름 끝에 있는 최종 SQL 실행 태스크(또는 필요에 따라 2개)에서 CREATE 스크립트 및 CHECK 스크립트를 연결하기 전에 필요한 기타 ETL 프로세스를 수행합니다.
sync_CreateFK에서 *를 선택하고 쿼리 창에 복사/붙여넣기, 한 번에 하나씩 실행하며 여전히 재적용에 실패하거나 실패한 데이터를 발견하면 데이터 문제를 수정할 수 있으므로 실제 테이블에 스크립트를 저장하는 것이 매우 중요합니다.
스크립트를 다시 실행하기 전에 모든 외부 키/체크를 다시 적용하지 않고 스크립트를 다시 실행하지 마십시오.그렇지 않으면 실행할 스크립트를 작성하기 전에 스테이징 테이블이 삭제되고 다시 작성되기 때문에 작성과 체크가 손실될 수 있습니다.
----------------------------------------------------------------------------
1)
/*
Author: Denmach
DateCreated: 2014-04-23
Purpose: Generates SQL statements to DROP, ADD, and CHECK existing constraints for a
database. Stores scripts in tables on target database for execution. Executes
those stored scripts via independent cursors.
DateModified:
ModifiedBy
Comments: This will eliminate deletes and the T-log ballooning associated with it.
*/
DECLARE @schema_name SYSNAME;
DECLARE @table_name SYSNAME;
DECLARE @constraint_name SYSNAME;
DECLARE @constraint_object_id INT;
DECLARE @referenced_object_name SYSNAME;
DECLARE @is_disabled BIT;
DECLARE @is_not_for_replication BIT;
DECLARE @is_not_trusted BIT;
DECLARE @delete_referential_action TINYINT;
DECLARE @update_referential_action TINYINT;
DECLARE @tsql NVARCHAR(4000);
DECLARE @tsql2 NVARCHAR(4000);
DECLARE @fkCol SYSNAME;
DECLARE @pkCol SYSNAME;
DECLARE @col1 BIT;
DECLARE @action CHAR(6);
DECLARE @referenced_schema_name SYSNAME;
--------------------------------Generate scripts to drop all foreign keys in a database --------------------------------
IF OBJECT_ID('dbo.sync_dropFK') IS NOT NULL
DROP TABLE sync_dropFK
CREATE TABLE sync_dropFK
(
ID INT IDENTITY (1,1) NOT NULL
, Script NVARCHAR(4000)
)
DECLARE FKcursor CURSOR FOR
SELECT
OBJECT_SCHEMA_NAME(parent_object_id)
, OBJECT_NAME(parent_object_id)
, name
FROM
sys.foreign_keys WITH (NOLOCK)
ORDER BY
1,2;
OPEN FKcursor;
FETCH NEXT FROM FKcursor INTO
@schema_name
, @table_name
, @constraint_name
WHILE @@FETCH_STATUS = 0
BEGIN
SET @tsql = 'ALTER TABLE '
+ QUOTENAME(@schema_name)
+ '.'
+ QUOTENAME(@table_name)
+ ' DROP CONSTRAINT '
+ QUOTENAME(@constraint_name)
+ ';';
--PRINT @tsql;
INSERT sync_dropFK (
Script
)
VALUES (
@tsql
)
FETCH NEXT FROM FKcursor INTO
@schema_name
, @table_name
, @constraint_name
;
END;
CLOSE FKcursor;
DEALLOCATE FKcursor;
---------------Generate scripts to create all existing foreign keys in a database --------------------------------
----------------------------------------------------------------------------------------------------------
IF OBJECT_ID('dbo.sync_createFK') IS NOT NULL
DROP TABLE sync_createFK
CREATE TABLE sync_createFK
(
ID INT IDENTITY (1,1) NOT NULL
, Script NVARCHAR(4000)
)
IF OBJECT_ID('dbo.sync_createCHECK') IS NOT NULL
DROP TABLE sync_createCHECK
CREATE TABLE sync_createCHECK
(
ID INT IDENTITY (1,1) NOT NULL
, Script NVARCHAR(4000)
)
DECLARE FKcursor CURSOR FOR
SELECT
OBJECT_SCHEMA_NAME(parent_object_id)
, OBJECT_NAME(parent_object_id)
, name
, OBJECT_NAME(referenced_object_id)
, OBJECT_ID
, is_disabled
, is_not_for_replication
, is_not_trusted
, delete_referential_action
, update_referential_action
, OBJECT_SCHEMA_NAME(referenced_object_id)
FROM
sys.foreign_keys WITH (NOLOCK)
ORDER BY
1,2;
OPEN FKcursor;
FETCH NEXT FROM FKcursor INTO
@schema_name
, @table_name
, @constraint_name
, @referenced_object_name
, @constraint_object_id
, @is_disabled
, @is_not_for_replication
, @is_not_trusted
, @delete_referential_action
, @update_referential_action
, @referenced_schema_name;
WHILE @@FETCH_STATUS = 0
BEGIN
BEGIN
SET @tsql = 'ALTER TABLE '
+ QUOTENAME(@schema_name)
+ '.'
+ QUOTENAME(@table_name)
+ CASE
@is_not_trusted
WHEN 0 THEN ' WITH CHECK '
ELSE ' WITH NOCHECK '
END
+ ' ADD CONSTRAINT '
+ QUOTENAME(@constraint_name)
+ ' FOREIGN KEY (';
SET @tsql2 = '';
DECLARE ColumnCursor CURSOR FOR
SELECT
COL_NAME(fk.parent_object_id
, fkc.parent_column_id)
, COL_NAME(fk.referenced_object_id
, fkc.referenced_column_id)
FROM
sys.foreign_keys fk WITH (NOLOCK)
INNER JOIN sys.foreign_key_columns fkc WITH (NOLOCK) ON fk.[object_id] = fkc.constraint_object_id
WHERE
fkc.constraint_object_id = @constraint_object_id
ORDER BY
fkc.constraint_column_id;
OPEN ColumnCursor;
SET @col1 = 1;
FETCH NEXT FROM ColumnCursor INTO @fkCol, @pkCol;
WHILE @@FETCH_STATUS = 0
BEGIN
IF (@col1 = 1)
SET @col1 = 0;
ELSE
BEGIN
SET @tsql = @tsql + ',';
SET @tsql2 = @tsql2 + ',';
END;
SET @tsql = @tsql + QUOTENAME(@fkCol);
SET @tsql2 = @tsql2 + QUOTENAME(@pkCol);
--PRINT '@tsql = ' + @tsql
--PRINT '@tsql2 = ' + @tsql2
FETCH NEXT FROM ColumnCursor INTO @fkCol, @pkCol;
--PRINT 'FK Column ' + @fkCol
--PRINT 'PK Column ' + @pkCol
END;
CLOSE ColumnCursor;
DEALLOCATE ColumnCursor;
SET @tsql = @tsql + ' ) REFERENCES '
+ QUOTENAME(@referenced_schema_name)
+ '.'
+ QUOTENAME(@referenced_object_name)
+ ' ('
+ @tsql2 + ')';
SET @tsql = @tsql
+ ' ON UPDATE '
+
CASE @update_referential_action
WHEN 0 THEN 'NO ACTION '
WHEN 1 THEN 'CASCADE '
WHEN 2 THEN 'SET NULL '
ELSE 'SET DEFAULT '
END
+ ' ON DELETE '
+
CASE @delete_referential_action
WHEN 0 THEN 'NO ACTION '
WHEN 1 THEN 'CASCADE '
WHEN 2 THEN 'SET NULL '
ELSE 'SET DEFAULT '
END
+
CASE @is_not_for_replication
WHEN 1 THEN ' NOT FOR REPLICATION '
ELSE ''
END
+ ';';
END;
-- PRINT @tsql
INSERT sync_createFK
(
Script
)
VALUES (
@tsql
)
-------------------Generate CHECK CONSTRAINT scripts for a database ------------------------------
----------------------------------------------------------------------------------------------------------
BEGIN
SET @tsql = 'ALTER TABLE '
+ QUOTENAME(@schema_name)
+ '.'
+ QUOTENAME(@table_name)
+
CASE @is_disabled
WHEN 0 THEN ' CHECK '
ELSE ' NOCHECK '
END
+ 'CONSTRAINT '
+ QUOTENAME(@constraint_name)
+ ';';
--PRINT @tsql;
INSERT sync_createCHECK
(
Script
)
VALUES (
@tsql
)
END;
FETCH NEXT FROM FKcursor INTO
@schema_name
, @table_name
, @constraint_name
, @referenced_object_name
, @constraint_object_id
, @is_disabled
, @is_not_for_replication
, @is_not_trusted
, @delete_referential_action
, @update_referential_action
, @referenced_schema_name;
END;
CLOSE FKcursor;
DEALLOCATE FKcursor;
--SELECT * FROM sync_DropFK
--SELECT * FROM sync_CreateFK
--SELECT * FROM sync_CreateCHECK
---------------------------------------------------------------------------
2.)
-----------------------------------------------------------------------------------------------------------------
----------------------------execute Drop FK Scripts --------------------------------------------------
DECLARE @scriptD NVARCHAR(4000)
DECLARE DropFKCursor CURSOR FOR
SELECT Script
FROM sync_dropFK WITH (NOLOCK)
OPEN DropFKCursor
FETCH NEXT FROM DropFKCursor
INTO @scriptD
WHILE @@FETCH_STATUS = 0
BEGIN
--PRINT @scriptD
EXEC (@scriptD)
FETCH NEXT FROM DropFKCursor
INTO @scriptD
END
CLOSE DropFKCursor
DEALLOCATE DropFKCursor
--------------------------------------------------------------------------------
3.)
------------------------------------------------------------------------------------------------------------------
----------------------------Truncate all tables in the database other than our staging tables --------------------
------------------------------------------------------------------------------------------------------------------
EXEC sp_MSforeachtable 'IF OBJECT_ID(''?'') NOT IN
(
ISNULL(OBJECT_ID(''dbo.sync_createCHECK''),0),
ISNULL(OBJECT_ID(''dbo.sync_createFK''),0),
ISNULL(OBJECT_ID(''dbo.sync_dropFK''),0)
)
BEGIN TRY
TRUNCATE TABLE ?
END TRY
BEGIN CATCH
PRINT ''Truncation failed on''+ ? +''
END CATCH;'
GO
-------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------
----------------------------execute Create FK Scripts and CHECK CONSTRAINT Scripts---------------
----------------------------tack me at the end of the ETL in a SQL task-------------------------
-------------------------------------------------------------------------------------------------
DECLARE @scriptC NVARCHAR(4000)
DECLARE CreateFKCursor CURSOR FOR
SELECT Script
FROM sync_createFK WITH (NOLOCK)
OPEN CreateFKCursor
FETCH NEXT FROM CreateFKCursor
INTO @scriptC
WHILE @@FETCH_STATUS = 0
BEGIN
--PRINT @scriptC
EXEC (@scriptC)
FETCH NEXT FROM CreateFKCursor
INTO @scriptC
END
CLOSE CreateFKCursor
DEALLOCATE CreateFKCursor
-------------------------------------------------------------------------------------------------
DECLARE @scriptCh NVARCHAR(4000)
DECLARE CreateCHECKCursor CURSOR FOR
SELECT Script
FROM sync_createCHECK WITH (NOLOCK)
OPEN CreateCHECKCursor
FETCH NEXT FROM CreateCHECKCursor
INTO @scriptCh
WHILE @@FETCH_STATUS = 0
BEGIN
--PRINT @scriptCh
EXEC (@scriptCh)
FETCH NEXT FROM CreateCHECKCursor
INTO @scriptCh
END
CLOSE CreateCHECKCursor
DEALLOCATE CreateCHECKCursor
제약 조건을 찾다
SELECT *
FROM sys.foreign_keys
WHERE referenced_object_id = object_id('TABLE_NAME')
이 SQL에서 생성된 SQL 실행
SELECT
'ALTER TABLE ' + OBJECT_SCHEMA_NAME(parent_object_id) +
'.[' + OBJECT_NAME(parent_object_id) +
'] DROP CONSTRAINT ' + name
FROM sys.foreign_keys
WHERE referenced_object_id = object_id('TABLE_NAME')
세이프웨이
주의: 구속조건을 폐기하기 위한 솔루션을 추가했습니다.이것에 의해, 제약조건의 에러 없이 테이블을 폐기 또는 변경할 수 있습니다.
'905'로 표시된 답변은 보기에는 양호하지만 작동하지 않습니다.
팔로잉은 나에게 효과가 있었다.기본 키, 고유 키 또는 기본 제약 조건은 비활성화할 수 없습니다.실제로 'sp_helpconstraint'가 status_enabled에 'n/a'로 표시되는 경우 활성화/비활성화할 수 없음을 의미합니다.
-- DISABLE 스크립트를 생성합니다.
select 'ALTER TABLE ' + object_name(id) + ' NOCHECK CONSTRAINT [' + object_name(constid) + ']'
from sys.sysconstraints
where status & 0x4813 = 0x813 order by object_name(id)
-- 이네이블로 하는 스크립트를 생성합니다.
select 'ALTER TABLE ' + object_name(id) + ' CHECK CONSTRAINT [' + object_name(constid) + ']'
from sys.sysconstraints
where status & 0x4813 = 0x813 order by object_name(id)
다른 제약을 일시적으로 비활성화하는 것과 같은 방법으로 외부 키 제약을 비활성화할 수 있어야 합니다.
Alter table MyTable nocheck constraint FK_ForeignKeyConstraintName
제약 조건 이름에 나열된 첫 번째 테이블에서 제약 조건을 사용 불가능으로 설정합니다.예를 들어 외부 키 제약이 FK_LocationsEmployeeLocationIdEmployeeId인 경우 다음을 사용합니다.
Alter table Locations nocheck constraint FK_LocationsEmployeesLocationIdEmployeeId
이 제약조건을 위반하면 반드시 해당 테이블을 충돌의 근원으로 명시하지 않는 오류가 발생합니다.
테이블 설계를 오른쪽 클릭하여 [Relations]으로 이동하고 왼쪽 페인으로 [Enforce fore foreign key contraint]를 [Yes](외부 키 제약 조건을 활성화하려면 [No](비활성화하려면)로 설정합니다.
모든 것을 규정하는 하나의 스크립트: sp_MSforeachable과 truncate 명령어와 delete 명령어를 조합하여 제약조건의 폐기 및 재작성을 회피합니다.절단하지 않고 삭제할 필요가 있는 테이블을 지정하기만 하면 됩니다.또한 적절한 측정을 위해 추가 스키마 필터를 추가했습니다(2008r2에서 테스트 완료).
declare @schema nvarchar(max) = 'and Schema_Id=Schema_id(''Value'')'
declare @deletiontables nvarchar(max) = '(''TableA'',''TableB'')'
declare @truncateclause nvarchar(max) = @schema + ' and o.Name not in ' + + @deletiontables;
declare @deleteclause nvarchar(max) = @schema + ' and o.Name in ' + @deletiontables;
exec sp_MSforeachtable 'alter table ? nocheck constraint all', @whereand=@schema
exec sp_MSforeachtable 'truncate table ?', @whereand=@truncateclause
exec sp_MSforeachtable 'delete from ?', @whereand=@deleteclause
exec sp_MSforeachtable 'alter table ? with check check constraint all', @whereand=@schema
테이블에서 일시적으로 구속조건을 해제하고 작업을 수행한 후 재구축할 수 있습니다.
여기 간단한 방법이 있습니다.
기본 키를 포함한 모든 인덱스를 비활성화하여 모든 외부 키를 비활성화한 다음 기본 키만 다시 활성화하여 작업할 수 있도록 합니다.
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
더 유용한 버전이 있습니다.링크가 더 이상 활성화되지 않는 웹사이트에서 약간의 코드를 제거했습니다.테이블 배열을 저장 프로시저에 넣을 수 있도록 수정했습니다.모든 것을 실행하기 전에 드롭, 잘라내기, 추가문을 채웁니다.이를 통해 잘라내야 하는 테이블을 결정할 수 있습니다.
/****** Object: UserDefinedTableType [util].[typ_objects_for_managing] Script Date: 03/04/2016 16:42:55 ******/
CREATE TYPE [util].[typ_objects_for_managing] AS TABLE(
[schema] [sysname] NOT NULL,
[object] [sysname] NOT NULL
)
GO
create procedure [util].[truncate_table_with_constraints]
@objects_for_managing util.typ_objects_for_managing readonly
--@schema sysname
--,@table sysname
as
--select
-- @table = 'TABLE',
-- @schema = 'SCHEMA'
declare @exec_table as table (ordinal int identity (1,1), statement nvarchar(4000), primary key (ordinal));
--print '/*Drop Foreign Key Statements for ['+@schema+'].['+@table+']*/'
insert into @exec_table (statement)
select
'ALTER TABLE ['+SCHEMA_NAME(o.schema_id)+'].['+ o.name+'] DROP CONSTRAINT ['+fk.name+']'
from sys.foreign_keys fk
inner join sys.objects o
on fk.parent_object_id = o.object_id
where
exists (
select * from @objects_for_managing chk
where
chk.[schema] = SCHEMA_NAME(o.schema_id)
and
chk.[object] = o.name
)
;
--o.name = @table and
--SCHEMA_NAME(o.schema_id) = @schema
insert into @exec_table (statement)
select
'TRUNCATE TABLE ' + src.[schema] + '.' + src.[object]
from @objects_for_managing src
;
--print '/*Create Foreign Key Statements for ['+@schema+'].['+@table+']*/'
insert into @exec_table (statement)
select 'ALTER TABLE ['+SCHEMA_NAME(o.schema_id)+'].['+o.name+'] ADD CONSTRAINT ['+fk.name+'] FOREIGN KEY (['+c.name+'])
REFERENCES ['+SCHEMA_NAME(refob.schema_id)+'].['+refob.name+'](['+refcol.name+'])'
from sys.foreign_key_columns fkc
inner join sys.foreign_keys fk
on fkc.constraint_object_id = fk.object_id
inner join sys.objects o
on fk.parent_object_id = o.object_id
inner join sys.columns c
on fkc.parent_column_id = c.column_id and
o.object_id = c.object_id
inner join sys.objects refob
on fkc.referenced_object_id = refob.object_id
inner join sys.columns refcol
on fkc.referenced_column_id = refcol.column_id and
fkc.referenced_object_id = refcol.object_id
where
exists (
select * from @objects_for_managing chk
where
chk.[schema] = SCHEMA_NAME(o.schema_id)
and
chk.[object] = o.name
)
;
--o.name = @table and
--SCHEMA_NAME(o.schema_id) = @schema
declare @looper int , @total_records int, @sql_exec nvarchar(4000)
select @looper = 1, @total_records = count(*) from @exec_table;
while @looper <= @total_records
begin
select @sql_exec = (select statement from @exec_table where ordinal =@looper)
exec sp_executesql @sql_exec
print @sql_exec
set @looper = @looper + 1
end
ALTER TABLE Name NOCHECK RESTRACT ALL을 사용하여 구속조건을 쉽게 해제할 수 있습니다.
트랜잭션을 완료한 후 ALTER TABLE Name CHECK RESTRACT ALL을 사용하여 트랜잭션을 다시 켜는 것을 잊지 마십시오.
언급URL : https://stackoverflow.com/questions/159038/how-can-foreign-key-constraints-be-temporarily-disabled-using-t-sql
반응형
'itsource' 카테고리의 다른 글
| Bash 이력에 명령어가 표시되지 않도록 하려면 어떻게 해야 합니까? (0) | 2023.04.17 |
|---|---|
| 로컬 파일을 생성하지 않고 OpenXML을 사용하여 Excel 파일을 생성하는 방법은 무엇입니까? (0) | 2023.04.17 |
| SQL Server Management Studio 2008에서 스크립트 생성 태스크를 자동화하려면 어떻게 해야 합니까? (0) | 2023.04.17 |
| UILabel 텍스트를 가운데에 정렬 (0) | 2023.04.12 |
| OSX 터미널에서 링크를 클릭할 수 있는 방법이 있습니까? (0) | 2023.04.12 |