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?

sql  sql-server  tsql  check-constraints 
— Wayne Molina
źródło

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.

— Chris Hynes
źródło
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;
— Graeme
źródło
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".
— Greenstone Walker
źródło
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 ...

— południe i
źródło
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.

— John Sansom
źródło
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.

— Christian Hayter
źródło
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;
— Graeme
źródło
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.