반응형
T-SQL을 사용하여 외래 키 제약 조건을 일시적으로 비활성화하려면 어떻게해야합니까?
SQL Server에서 외래 키 제약 조건을 비활성화 및 활성화합니까? 아니면 내 유일한 옵션은 drop다음 재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"
한 데이터베이스에서 다른 데이터베이스로 데이터를 채울 때 유용합니다. 제약 조건을 삭제하는 것보다 훨씬 더 나은 접근 방식입니다. 언급했듯이 데이터베이스의 모든 데이터를 삭제하고 다시 채울 때 유용합니다 (예 : 테스트 환경).
모든 데이터를 삭제하는 경우이 솔루션 이 도움 이 될 수 있습니다.
또한 때로는 모든 트리거를 비활성화하는 것이 편리합니다 . 여기 에서 전체 솔루션을 볼 수 있습니다 .
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
제약 조건을 비활성화하려면 NOCHECK를 사용하여 테이블을 ALTER하십시오.
ALTER TABLE [TABLE_NAME] NOCHECK CONSTRAINT [ALL|CONSTRAINT_NAME]
사용하려면 double CHECK 를 사용해야합니다.
ALTER TABLE [TABLE_NAME] WITH CHECK CHECK CONSTRAINT [ALL|CONSTRAINT_NAME]
- 활성화 할 때 이중 CHECK 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
가장 좋은 방법은 외래 키 제약 조건을 DROP 및 CREATE하는 것입니다.
이 게시물에서 "있는 그대로"작동하는 예제를 찾지 못했습니다. 하나는 외래 키가 다른 스키마를 참조하면 작동하지 않고 다른 하나는 외래 키가 여러 열을 참조하면 작동하지 않습니다. 이 스크립트는 외래 키당 여러 스키마와 여러 열을 모두 고려합니다.
다음은 "ADD CONSTRAINT"문을 생성하는 스크립트입니다. 여러 열의 경우 쉼표로 구분합니다 ( 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 CONSTRAINT"문을 생성하는 스크립트입니다.
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 표준은 constaint를 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
동의합니다, Hamlin. SSIS를 사용하여 데이터를 전송하거나 데이터를 복제하려는 경우 외래 키 제약 조건을 일시적으로 비활성화하거나 삭제 한 다음 다시 활성화하거나 다시 만들어야합니다. 이러한 경우 참조 무결성은 소스 데이터베이스에서 이미 유지 관리되기 때문에 문제가되지 않습니다. 따라서이 문제에 대해 안심할 수 있습니다.
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 처리 될 수 있습니다.
여기에는 DROP, CREATE 및 CHECK FK 스크립트를 수용하기위한 세 개의 스테이징 테이블 (실제 테이블) 생성, 이러한 스크립트를 테이블에 생성 및 삽입 한 다음 테이블을 반복하고 실행하는 작업이 포함됩니다. 첨부 된 스크립트는 다음 네 부분으로 구성됩니다. 1.) 3 개의 스테이징 (실제) 테이블에 스크립트 생성 및 저장, 2.) 커서를 통한 드롭 FK 스크립트 실행, 3.) sp_MSforeachtable을 사용하여 모든 3 개의 스테이징 테이블 이외의 데이터베이스에있는 테이블 및 4.) 생성 FK 실행 및 ETL SSIS 패키지 끝에서 FK 스크립트 확인.
SSIS의 SQL 실행 작업에서 스크립트 생성 부분을 실행합니다. 두 번째 SQL 실행 태스크에서 "execute Drop FK Scripts"부분을 실행합니다. 자르기 스크립트를 세 번째 SQL 실행 작업에 넣은 다음 제어 흐름 끝에있는 최종 SQL 실행 작업 (또는 원하는 경우 두 개)에 CREATE 및 CHECK 스크립트를 연결하기 전에 수행해야하는 다른 ETL 프로세스를 수행합니다.
sync_CreateFK에서 *를 선택하고, 쿼리 창에 복사 / 붙여 넣기하고, 한 번에 하나씩 실행하고, 데이터 문제를 해결할 수 있으므로 외래 키의 재 적용이 실패 할 때 실제 테이블에 스크립트를 저장하는 것은 매우 중요합니다. 실패했거나 여전히 재 신청에 실패한 사람을 찾으십시오.
그렇게하기 전에 모든 외래 키 / 검사를 다시 적용했는지 확인하지 않고 실패한 경우 스크립트를 다시 실행하지 마십시오. 그렇지 않으면 준비 테이블이 삭제되고 fk 스크립팅이 생성되고 검사 될 가능성이 큽니다. 실행할 스크립트를 생성하기 전에 다시 생성됩니다.
----------------------------------------------------------------------------
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
WITH CHECK CHECK 거의 확실하게 필요합니다!
이 점은 일부 답변과 의견에서 제기되었지만 다시 언급하는 것이 중요하다고 생각합니다.
다음 명령 (no WITH CHECK)을 사용하여 제약 조건을 다시 활성화하면 몇 가지 심각한 단점이 있습니다.
ALTER TABLE MyTable CHECK CONSTRAINT MyConstraint;
체크 있음 | NOCHECK 포함
테이블의 데이터가 새로 추가되거나 다시 활성화 된 FOREIGN KEY 또는 CHECK 제약 조건에 대해 유효성을 검사하는지 여부를 지정합니다. 지정하지 않으면 WITH CHECK는 새 제약 조건에 대해 가정하고 WITH NOCHECK는 다시 활성화 된 제약 조건에 대해 가정합니다.
기존 데이터에 대해 새 CHECK 또는 FOREIGN KEY 제약 조건을 확인하지 않으려면 WITH NOCHECK를 사용하십시오. 드문 경우를 제외하고는 이렇게하지 않는 것이 좋습니다. 새로운 제약 조건은 이후의 모든 데이터 업데이트에서 평가됩니다. 제약 조건이 추가 될 때 WITH NOCHECK에 의해 억제되는 제약 조건 위반으로 인해 제약 조건을 준수하지 않는 데이터로 행을 업데이트하는 경우 향후 업데이트가 실패 할 수 있습니다.
쿼리 최적화 프로그램은 WITH NOCHECK로 정의 된 제약 조건을 고려하지 않습니다. 이러한 제약 조건은 ALTER TABLE table WITH CHECK CHECK CONSTRAINT ALL을 사용하여 다시 활성화 될 때까지 무시됩니다.
참고 : WITH NOCHECK는 제약 조건을 다시 활성화하기위한 기본값입니다. 왜 그런지 궁금해서 ...
- 이 명령을 실행하는 동안 테이블에있는 기존 데이터는 평가되지 않습니다. 성공적으로 완료되었다고해서 제약 조건에 따라 테이블의 데이터가 유효하다는 보장은 없습니다.
- 유효하지 않은 레코드의 다음 업데이트 동안 제약 조건이 평가되고 실패하므로 실제 업데이트와 관련이없는 오류가 발생할 수 있습니다.
- 데이터가 유효한지 확인하기 위해 제약 조건에 의존하는 응용 프로그램 논리가 실패 할 수 있습니다.
- 쿼리 최적화 프로그램은 이러한 방식으로 활성화 된 제약 조건을 사용하지 않습니다.
sys.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;
제약 조건 찾기
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'로 표시된 답변은 괜찮아 보이지만 작동하지 않습니다.
다음은 나를 위해 일했습니다. 기본 키, 고유 키 또는 기본 제약 조건 은 비활성화 할 수 없습니다 . 사실, status_enabled에서 'sp_helpconstraint' '쇼'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_LocationsEmployeesLocationIdEmployeeId 인 경우 다음을 사용하고 싶습니다.
Alter table Locations nocheck constraint FK_LocationsEmployeesLocationIdEmployeeId
이 제약 조건을 위반하더라도 해당 테이블을 충돌의 원인으로 명시하지 않아도되는 오류가 발생합니다.
모두를 지배하는 하나의 스크립트 : sp_MSforeachtable과 함께 자르기 및 삭제 명령을 결합하여 제약 조건을 삭제하고 다시 만드는 것을 방지 할 수 있습니다. 잘라 내기보다는 삭제해야하는 테이블 만 지정하고 제 목적을 위해 추가 스키마 필터를 추가했습니다. 측정 (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
관심이 있으시면 더 유용한 버전이 있습니다. 링크가 더 이상 활성화되지 않은 웹 사이트에서 약간의 코드를 가져 왔습니다. 저장 프로 시저에 테이블 배열을 허용하도록 수정했으며 모든 항목을 실행하기 전에 drop, truncate, add 문을 채웠습니다. 이렇게하면 자르기가 필요한 테이블을 결정할 수 있습니다.
/****** 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
반응형
'programing tip' 카테고리의 다른 글
| Git 커밋 수는 어떻게 얻습니까? (0) | 2020.09.30 |
|---|---|
| 파이썬의 super ()는 다중 상속과 어떻게 작동합니까? (0) | 2020.09.29 |
| % w (array)는 무엇을 의미합니까? (0) | 2020.09.29 |
| 경계 내에 맞게 TextView 텍스트 자동 크기 조정 (0) | 2020.09.29 |
| Collatz 추측을 수작업으로 작성한 어셈블리보다 빠르게 테스트하기위한 C ++ 코드-왜? (0) | 2020.09.29 |