AVEC VÉRIFICATION AJOUTER CONTRAINT suivi de VÉRIFICATION CONTRAINT vs. AJOUTER CONTRAINT

Question

Je suis à la recherche à la base de données AdventureWorks pour SQL Server 2008, et je vois dans leurs scripts de création qu'ils ont tendance à utiliser les éléments suivants:

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

immédiatement suivie par :

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

Je vois ce pour les clés étrangères (comme ici), les contraintes unique et régulier CHECK contraintes; DEFAULT contraintes utiliser le format que je suis plus familier avec tels que:

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

Quelle est la différence entre faire le premier rapport à la seconde?

Answer 1

La première syntaxe est redondante - WITH CHECK est la valeur par défaut et la contrainte est également activée par défaut. Cette syntaxe est générée par le studio de gestion SQL lors de la génération de scripts SQL. J'imagine que c'est une sorte de redondance supplémentaire, éventuellement pour s'assurer que la contrainte est activée même si le comportement de contrainte par défaut pour une table est modifié.

Answer 2

Pour démontrer comment cela fonctionne--

 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;
 

Answer 3

Outre ce qui précède, d'excellents commentaires au sujet de confiance des contraintes:

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

Pas de confiance contrainte, comme son nom l'indique, ne peut pas être digne de confiance pour représenter avec précision l'état des données dans la table dès maintenant. Il peut, cependant, mais peut faire confiance à vérifier les données ajoutées et modifiées dans le futur.

En outre, non approuvé les contraintes sont négligés par l'optimiseur de requête.

Le code pour activer la vérification de contraintes et les contraintes de clé étrangère est assez mauvais, avec trois significations du mot "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".

Answer 4

WITH NOCHECK est également utilisé lorsque des données existantes dans une table ne sont pas conformes à la contrainte définie et que vous ne souhaitez pas que celles-ci dépassent la nouvelle contrainte que vous implémentez. .

Answer 5

``est en effet le comportement par défaut mais il est conseillé d’inclure dans vos codes.

Le comportement alternatif est bien sûr d’utiliser `` , il est donc bon de définir explicitement vos intentions. Ceci est souvent utilisé lorsque vous jouez avec/modifier/changer les partitions en ligne.