[sql] CHECK ADD CONSTRAINT 후 CHECK CONSTRAINT vs. ADD CONSTRAINT

Program / 글쓴이

SQL Server 2008 용 AdventureWorks 예제 데이터베이스를보고 있는데 작성 스크립트에서 다음을 사용하는 경향이 있음을 알 수 있습니다.

ALTER TABLE [Production].[ProductCostHistory] WITH CHECK ADD
CONSTRAINT [FK_ProductCostHistory_Product_ProductID] FOREIGN KEY([ProductID])
  REFERENCES [Production].[Product] ([ProductID])
GO

바로 뒤에 :

ALTER TABLE [Production].[ProductCostHistory] CHECK CONSTRAINT
[FK_ProductCostHistory_Product_ProductID]
GO

외래 키 (여기서와 같이), 고유 제약 조건 및 일반 CHECK제약 조건에서 이것을 봅니다 . DEFAULT제약 조건은 다음과 같이 더 익숙한 정규 형식을 사용합니다.

ALTER TABLE [Production].[ProductCostHistory] ADD  CONSTRAINT
[DF_ProductCostHistory_ModifiedDate]  DEFAULT (getdate()) FOR [ModifiedDate]
GO

첫 번째 방법과 두 번째 방법의 차이점은 무엇입니까?

답변

첫 번째 구문은 중복입니다. WITH CHECK는 새 제약 조건의 기본값이며 제약 조건도 기본적으로 설정되어 있습니다.

이 구문은 SQL 스크립트를 생성 할 때 SQL 관리 스튜디오에서 생성합니다. 테이블의 기본 제약 조건 동작이 변경 되어도 제약 조건이 활성화되도록 일종의 여분의 중복성이 있다고 가정합니다.

답변

이것이 어떻게 작동하는지 보여주기 위해-

CREATE TABLE T1 (ID INT NOT NULL, SomeVal CHAR(1));
ALTER TABLE T1 ADD CONSTRAINT [PK_ID] PRIMARY KEY CLUSTERED (ID);

CREATE TABLE T2 (FKID INT, SomeOtherVal CHAR(2));

INSERT T1 (ID, SomeVal) SELECT 1, 'A';
INSERT T1 (ID, SomeVal) SELECT 2, 'B';

INSERT T2 (FKID, SomeOtherVal) SELECT 1, 'A1';
INSERT T2 (FKID, SomeOtherVal) SELECT 1, 'A2';
INSERT T2 (FKID, SomeOtherVal) SELECT 2, 'B1';
INSERT T2 (FKID, SomeOtherVal) SELECT 2, 'B2';
INSERT T2 (FKID, SomeOtherVal) SELECT 3, 'C1';  --orphan
INSERT T2 (FKID, SomeOtherVal) SELECT 3, 'C2';  --orphan

--Add the FK CONSTRAINT will fail because of existing orphaned records
ALTER TABLE T2 ADD CONSTRAINT FK_T2_T1 FOREIGN KEY (FKID) REFERENCES T1 (ID);   --fails

--Same as ADD above, but explicitly states the intent to CHECK the FK values before creating the CONSTRAINT
ALTER TABLE T2 WITH CHECK ADD CONSTRAINT FK_T2_T1 FOREIGN KEY (FKID) REFERENCES T1 (ID);    --fails

--Add the CONSTRAINT without checking existing values
ALTER TABLE T2 WITH NOCHECK ADD CONSTRAINT FK_T2_T1 FOREIGN KEY (FKID) REFERENCES T1 (ID);  --succeeds
ALTER TABLE T2 CHECK CONSTRAINT FK_T2_T1;   --succeeds since the CONSTRAINT is attributed as NOCHECK

--Attempt to enable CONSTRAINT fails due to orphans
ALTER TABLE T2 WITH CHECK CHECK CONSTRAINT FK_T2_T1;    --fails

--Remove orphans
DELETE FROM T2 WHERE FKID NOT IN (SELECT ID FROM T1);

--Enabling the CONSTRAINT succeeds
ALTER TABLE T2 WITH CHECK CHECK CONSTRAINT FK_T2_T1;    --succeeds; orphans removed

--Clean up
DROP TABLE T2;
DROP TABLE T1;

답변

신뢰할 수있는 제약 조건에 대한 위의 우수한 의견 외에도 :

select * from sys.foreign_keys where is_not_trusted = 1 ;
select * from sys.check_constraints where is_not_trusted = 1 ;

이름에서 알 수 있듯이 신뢰할 수없는 제약 조건은 현재 테이블의 데이터 상태를 정확하게 나타 내기 위해 신뢰할 수 없습니다. 그러나 향후 추가 및 수정 된 데이터를 확인하는 것은 신뢰할 수 있습니다.

또한 쿼리 최적화 프로그램에서는 신뢰할 수없는 제약 조건을 무시합니다.

검사 제한 조건 및 외래 키 제한 조건을 활성화하는 코드는 “check”라는 단어의 세 가지 의미로 상당히 나쁩니다.

ALTER TABLE [Production].[ProductCostHistory]
WITH CHECK -- This means "Check the existing data in the table".
CHECK CONSTRAINT -- This means "enable the check or foreign key constraint".
[FK_ProductCostHistory_Product_ProductID] -- The name of the check or foreign key constraint, or "ALL".

답변

WITH NOCHECK 정의 된 제약 조건을 준수하지 않는 테이블에 기존 데이터가 있고 구현하는 새 제약 조건을 무시하고 실행하지 않으려는 경우에도 사용됩니다 …

답변

WITH CHECK 실제로 기본 동작이지만 코딩에 포함시키는 것이 좋습니다.

대체 행동은 물론 사용하기 WITH NOCHECK때문에 의도를 명시 적으로 정의하는 것이 좋습니다. 인라인 파티션으로 재생 / 수정 / 전환 할 때 자주 사용됩니다.

답변

외래 키 및 검사 제약 조건은 활성화 또는 비활성화 될뿐만 아니라 신뢰할 수 있거나 신뢰할 수없는 개념입니다. 자세한 내용은 MSDN 페이지를 참조 ALTER TABLE하십시오.

WITH CHECK새 외래 키 및 검사 제약 조건을 추가하기위한 기본값이고, WITH NOCHECK비활성화 된 외래 키 및 검사 제약 조건을 다시 활성화하기위한 기본값입니다. 차이점을 인식하는 것이 중요합니다.

그럼에도 불구하고, 유틸리티에 의해 생성 된 명백하게 중복 된 진술은 단순히 안전 및 / 또는 코딩 용이성을 위해 존재한다. 걱정하지 마십시오.

답변

다음은 데이터베이스에서 신뢰할 수없는 제약 조건을 식별하고 수정하는 데 도움이되도록 작성한 코드입니다. 각 문제를 해결하는 코드를 생성합니다.

    ;WITH Untrusted (ConstraintType, ConstraintName, ConstraintTable, ParentTable, IsDisabled, IsNotForReplication, IsNotTrusted, RowIndex) AS
(
    SELECT
        'Untrusted FOREIGN KEY' AS FKType
        , fk.name AS FKName
        , OBJECT_NAME( fk.parent_object_id) AS FKTableName
        , OBJECT_NAME( fk.referenced_object_id) AS PKTableName
        , fk.is_disabled
        , fk.is_not_for_replication
        , fk.is_not_trusted
        , ROW_NUMBER() OVER (ORDER BY OBJECT_NAME( fk.parent_object_id), OBJECT_NAME( fk.referenced_object_id), fk.name) AS RowIndex
    FROM
        sys.foreign_keys fk
    WHERE
        is_ms_shipped = 0
        AND fk.is_not_trusted = 1

    UNION ALL

    SELECT
        'Untrusted CHECK' AS KType
        , cc.name AS CKName
        , OBJECT_NAME( cc.parent_object_id) AS CKTableName
        , NULL AS ParentTable
        , cc.is_disabled
        , cc.is_not_for_replication
        , cc.is_not_trusted
        , ROW_NUMBER() OVER (ORDER BY OBJECT_NAME( cc.parent_object_id), cc.name) AS RowIndex
    FROM
        sys.check_constraints cc
    WHERE
        cc.is_ms_shipped = 0
        AND cc.is_not_trusted = 1

)
SELECT
    u.ConstraintType
    , u.ConstraintName
    , u.ConstraintTable
    , u.ParentTable
    , u.IsDisabled
    , u.IsNotForReplication
    , u.IsNotTrusted
    , u.RowIndex
    , 'RAISERROR( ''Now CHECKing {%i of %i)--> %s ON TABLE %s'', 0, 1'
        + ', ' + CAST( u.RowIndex AS VARCHAR(64))
        + ', ' + CAST( x.CommandCount AS VARCHAR(64))
        + ', ' + '''' + QUOTENAME( u.ConstraintName) + ''''
        + ', ' + '''' + QUOTENAME( u.ConstraintTable) + ''''
        + ') WITH NOWAIT;'
    + 'ALTER TABLE ' + QUOTENAME( u.ConstraintTable) + ' WITH CHECK CHECK CONSTRAINT ' + QUOTENAME( u.ConstraintName) + ';' AS FIX_SQL
FROM Untrusted u
CROSS APPLY (SELECT COUNT(*) AS CommandCount FROM Untrusted WHERE ConstraintType = u.ConstraintType) x
ORDER BY ConstraintType, ConstraintTable, ParentTable;