CHECK ADD CONSTRAINT 후 CHECK CONSTRAINT vs. ADD CONSTRAINT

CHECK ADD CONSTRAINT 후 CHECK CONSTRAINT vs. ADD CONSTRAINT

---

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 is used as well when one has existing data in a table that doesn't conform to the constraint as defined and you don't want it to run afoul of the new constraint that you're implementing...

---

WITH CHECK is indeed the default behaviour however it is good practice to include within your coding.

The alternative behaviour is of course to use WITH NOCHECK, so it is good to explicitly define your intentions. This is often used when you are playing with/modifying/switching inline partitions.

---

Foreign key and check constraints have the concept of being trusted or untrusted, as well as being enabled and disabled. See the MSDN page for ALTER TABLE for full details.

WITH CHECK is the default for adding new foreign key and check constraints, WITH NOCHECK is the default for re-enabling disabled foreign key and check constraints. It's important to be aware of the difference.

Having said that, any apparently redundant statements generated by utilities are simply there for safety and/or ease of coding. Don't worry about them.

---

Here is some code I wrote to help us identify and correct untrusted CONSTRAINTs in a DATABASE. It generates the code to fix each issue.

    ;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;

참고URL : https://stackoverflow.com/questions/529941/with-check-add-constraint-followed-by-check-constraint-vs-add-constraint