CZEKAJ DODAJ OGRANICZENIE, a następnie SPRAWDŹ OGRANICZENIE vs. DODAJ OGRANICZENIE


137

Patrzę na przykładową bazę danych AdventureWorks dla SQL Server 2008 i widzę w ich skryptach do tworzenia, że ​​zwykle używają następujących elementów:

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

a zaraz po nim:

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

Widzę to dla kluczy obcych (tak jak tutaj), unikalnych ograniczeń i regularnych CHECKograniczeń; DEFAULTograniczenia używają zwykłego formatu, który jest mi bardziej znany, na przykład:

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

Jaka jest różnica, jeśli w ogóle, między robieniem tego w pierwszy sposób a drugim?

Odpowiedzi:


98

Pierwsza składnia jest nadmiarowa - WITH CHECKjest to wartość domyślna dla nowych ograniczeń, a ograniczenie jest również domyślnie włączone.

Ta składnia jest generowana przez studio zarządzania SQL podczas generowania skryptów sql - zakładam, że jest to jakiś rodzaj dodatkowej redundancji, prawdopodobnie w celu zapewnienia włączenia ograniczenia, nawet jeśli domyślne zachowanie ograniczenia dla tabeli zostanie zmienione.


12
Nie wygląda na to, że WITH CHECK jest w rzeczywistości wartością domyślną, jest tylko domyślną wartością dla nowych danych. Z msdn.microsoft.com/en-us/library/ms190273.aspx : „Jeśli nie zostanie określony, dla nowych ograniczeń zakłada się Z SPRAWDZENIEM, a dla ograniczeń ponownie włączonych.”
Zain Rizvi

9
@ZainRizvi: nie nowe dane, nowe ograniczenia. Jeśli wyłączysz ograniczenie za pomocą, ALTER TABLE foo NOCHECK CONSTRAINT fk_ba następnie włączysz je ALTER TABLE foo CHECK CONSTRAINT fk_bponownie, nie weryfikuje ograniczenia. ALTER TABLE foo WITH CHECK CHECK CONSTRAINT fk_bjest konieczne do zweryfikowania danych.
jmoreno

2
Czytanie tego początkowo nie było dla mnie jasne. Druga (nadmiarowa) linia to funkcja włączająca ograniczenie. Ponieważ ograniczenie jest domyślnie włączone, druga linia jest nadmiarowa.
blindguy

47

Aby pokazać, jak to działa,

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;

7
Porządkowanie-- DROP TABLE T2; DROP TABLE T1;
Graeme

8
Dodałem kod czyszczący z twojego komentarza do twojej rzeczywistej odpowiedzi, aby pomóc tamtym nocnym kopiującym i pasterzom.
mwolfe02

18
„Kopiarni i pasterze lecący nocą” wydaje się nieco negatywny. Uważam siebie za jednego z tych użytkowników stosu (dla bardziej pozytywnego sformułowania ...) „dla których tego typu szczegółowe przykłady są niezwykle cenne”.
GaTechThomas

2
To poniżające czy nie, „lataj nocą” wydaje mi się, że doskonale mnie opisuje.
sanepete

22

W nawiązaniu do powyższych doskonałych komentarzy na temat zaufanych ograniczeń:

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

Niezaufane ograniczenie, jak sugeruje jego nazwa, nie może być w tej chwili ufane, że dokładnie reprezentuje stan danych w tabeli. Można jednak, ale można ufać, że sprawdzi dane dodane i zmodyfikowane w przyszłości.

Ponadto niezaufane ograniczenia są ignorowane przez optymalizator zapytań.

Kod włączający ograniczenia sprawdzające i ograniczenia klucza obcego jest dość zły, z trzema znaczeniami słowa „sprawdź”.

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".

16

WITH NOCHECK jest również używany, gdy w tabeli istnieją dane, które nie są zgodne z ograniczeniem zdefiniowanym i nie chcesz, aby było ono sprzeczne z nowym ograniczeniem, które implementujesz ...


14

WITH CHECK jest rzeczywiście zachowaniem domyślnym, jednak dobrym zwyczajem jest uwzględnienie go w kodowaniu.

Alternatywne zachowanie jest oczywiście do zastosowania WITH NOCHECK, więc dobrze jest wyraźnie określić swoje zamiary. Jest to często używane, gdy grasz z / modyfikując / przełączając partycje wbudowane.


Ale, jak wspomniano w innych komentarzach, TYLKO domyślne ustawienie dla nowego ograniczenia.
d219

9

Klucz obcy i ograniczenia sprawdzające mają koncepcję bycia zaufanym lub niezaufanym, a także włączania i wyłączania. Zobacz stronę MSDN, aby ALTER TABLEuzyskać szczegółowe informacje.

WITH CHECKjest wartością domyślną do dodawania nowego klucza obcego i ograniczeń sprawdzania, WITH NOCHECKjest wartością domyślną do ponownego włączania wyłączonego klucza obcego i ograniczeń sprawdzania. Ważne jest, aby zdawać sobie sprawę z różnicy.

Mimo to wszelkie pozornie zbędne instrukcje generowane przez narzędzia są po prostu dostępne dla bezpieczeństwa i / lub łatwości kodowania. Nie martw się o nich.


Czy odnosisz się do tego łącza: msdn.microsoft.com/en-us/library/ms190273.aspx ? Czy to oznacza, że ​​musimy zrobić ALTER TABLE table With CHECK CHECK CONSTRAINT ALL zamiast robić to dla każdego ograniczenia?
Henrik Staun Poulsen

@HenrikStaunPoulsen: Tak, to jest link. Nic nie stoi na przeszkodzie, aby włączyć każde ograniczenie indywidualnie, ale musisz powiedzieć, WITH CHECK CHECK CONSTRAINTże zaufasz im.
Christian Hayter

Próbowałem tego; Uruchomiłem "ALTER TABLE [dfm]. [TRATransformError] WITH CHECK CHECK CONSTRAINT [FK_TRATransformError_ETLEvent]". Ale FK nadal ma Is_Not_Trusted = 1. Następnie porzuciłem FK i utworzyłem go ponownie za pomocą „WITH CHECK CHECK”, a teraz mam Is_Not_Trusted = 0. W końcu. Wiesz dlaczego? Zwróć uwagę, że zawsze miałem is_not_for_replication = 0
Henrik Staun Poulsen

@HenrikStaunPoulsen: Nie wiem, zawsze mi się to udało. Uruchamiam zapytanie, takie jak select * from sys.objects where [type] in ('C', 'F') and (objectproperty([object_id], 'CnstIsDisabled') = 1 or objectproperty([object_id], 'CnstIsNotTrusted') = 1)znalezienie wyłączonych i niezaufanych ograniczeń. Po wydaniu odpowiednich instrukcji alter table, jak powyżej, ograniczenia te znikają z zapytania, więc widzę, jak działa.
Christian Hayter

2
@HenrikStaunPoulsen, dzieje się tak, ponieważ flaga not_for_replication jest ustawiona na 1. To sprawia, że ​​ograniczenie jest niezaufane. SELECT name, create_date, modified_date, is_disabled, is_not_for_replication, is_not_trusted FROM sys.foreign_keys WHERE is_not_trusted = 1 W takim przypadku musisz usunąć i odtworzyć ograniczenie. Używam tego do osiągnięcia tego gist.github.com/smoothdeveloper/ea48e43aead426248c0f Pamiętaj, że podczas usuwania i aktualizacji nie są określone w tym skrypcie i musisz to wziąć pod uwagę.
kuklei,

8

Oto kod, który napisałem, aby pomóc nam zidentyfikować i skorygować niezaufane OGRANICZENIA w BAZIE DANYCH. Generuje kod, aby naprawić każdy problem.

    ;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;
Korzystając z naszej strony potwierdzasz, że przeczytałeś(-aś) i rozumiesz nasze zasady używania plików cookie i zasady ochrony prywatności.
Licensed under cc by-sa 3.0 with attribution required.