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;