[sql-server] T-SQL을 사용하여 외래 키 제약 조건을 어떻게 일시적으로 비활성화 할 수 있습니까?

Program / 글쓴이

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]

이중 CHECK 를 사용해야하는 경우 :

ALTER TABLE [TABLE_NAME] WITH CHECK CHECK CONSTRAINT [ALL|CONSTRAINT_NAME]
  • 활성화 할 때 이중 점검 확인에 주의하십시오 .
  • 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 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 표준을 사용하면 트랜잭션 범위 내에서 암시 적 또는 명시 적으로 지연 될 수 있도록 제약 조건을 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를 사용하여 데이터를 전송하거나 데이터를 복제하려는 경우 외래 키 제약 조건을 일시적으로 비활성화하거나 삭제 한 다음 다시 활성화하거나 다시 만들어야합니다. 이러한 경우 참조 무결성은 소스 데이터베이스에서 이미 유지 보수되므로 문제가되지 않습니다. 따라서이 문제에 대해 안심할 수 있습니다.

답변

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