MS SQL Server
  1. MS SQL Server
  3. Pourquoi ma contrainte check ne stoppe-t-elle pas cet insert nul?
Intereting Posts
Déclaration de nom de file dans une requête SQL Où déclaration avec sous-requête conditionnelle Horodatage SSMA. Qu'est-ce que c'est, comment est-il utilisé? Conception d'une relation de database conditionnelle dans SQL Server SQL Drop Index sur une database différente SQL Sélectionnez certaines valeurs en premier Procédure stockée avec des parameters DateTime facultatifs dans SQL Server Optimisation Sélectionner, supprimer, mettre à jour les performances des requêtes dans SQL Comment merge plusieurs loggings avec des strings et des valeurs NULL dans un groupe Stream Analytics SQL: Étant donné un logging, comment find tous ses loggings enfant associés sans connaître le nombre de niveaux dans la hiérarchie? SQL Server: clause Condition conditionnelle (Equal) OR (LessThan ou Equal) Comment corriger l'erreur "Doit déclarer la variable scalaire" lors du referencement de la variable table? sp_help avec des caractères generics SQL Server passant des identifiants aux procédures stockées / SQL dynamic Meilleure façon d'implémenter des tags (similaire à StackOverflow)

Pourquoi ma contrainte check ne stoppe-t-elle pas cet insert nul?

Quelqu'un peut-il expliquer pourquoi le troisième insert (appelé Query Data ) dans le code ci-dessous est autorisé par SQL Server?

Pour autant que je sache, la contrainte de vérification ne devrait permettre que:

  • Code est nul et le System est nul.
  • Code n'est pas nul et le System est 1 .

Ma première pensée était ANSI NULLS , mais ANSI NULLS ou les off ne faisait aucune différence.

Ceci est un exemple simplifié d'un problème plus important que nous avons trouvé dans notre application (le système a été vérifié par rapport à une list de nombres – IN(1, 2, etc.) ). Nous avons remplacé ce contrôle par une key étrangère (au lieu de IN ) et une nouvelle contrainte de vérification qui permettait soit null, soit les deux non null; Cela a empêché le troisième insert. 

 IF EXISTS (SELECT * FROM sys.check_constraints WHERE object_id = OBJECT_ID(N'[dbo].[CK_TestCheck]') AND parent_object_id = OBJECT_ID(N'[dbo].[TestCheck]')) ALTER TABLE [dbo].[TestCheck] DROP CONSTRAINT [CK_TestCheck] GO IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[TestCheck]') AND type in (N'U')) DROP TABLE [dbo].[TestCheck] GO SET ANSI_NULLS ON GO CREATE TABLE TestCheck( [Id] [int] IDENTITY(1,1) NOT NULL, [Code] [varchar](50) NULL, [System] [tinyint] NULL, PRIMARY KEY CLUSTERED ([Id] ASC)) GO ALTER TABLE [dbo].[TestCheck] WITH CHECK ADD CONSTRAINT [CK_TestCheck] CHECK ( ([Code] IS NULL AND [System] IS NULL) --Both null OR ([Code] IS NOT NULL AND [System] = 1) --Both not null ???? ) GO ALTER TABLE [dbo].[TestCheck] CHECK CONSTRAINT [CK_TestCheck] GO --Good Data insert TestCheck (Code, [System]) Values(null, null); insert TestCheck (Code, [System]) Values('123', 1); --Query Data insert TestCheck (Code, [System]) Values('123', null); --Bad data stopped insert TestCheck (Code, [System]) Values(null, 1); insert TestCheck (Code, [System]) Values('123', 4); select * from TestCheck Where case when ( ([Code] IS NULL AND [System] IS NULL) --Both null OR ([Code] IS NOT NULL AND [System] in (1, 2, 3)) --Both not null ???? ) then 0 else 1 end = 1

Le résultat de l'évaluation de la contrainte actuelle pour les valeurs 123, NULL est Undefined.

  • ([Code] IS NULL AND [System] IS NULL) évalue à False
  • ([Code] IS NOT NULL AND [System] IN (1, 2, 3)) évalue à Undefined

Le résultat est Undefined

Vérifier la contrainte

Les contraintes CHECK rejettent les valeurs évaluées à FALSE. Parce que les valeurs NULL évaluent à INCONNU, leur présence dans les expressions peut replace une contrainte.

Vous devez changer votre chèque pour [System] IN (1, 2, 3) à ISNULL([System], 0) IN (1, 2, 3) .

Votre contrainte de vérification devient alors 

 ALTER TABLE [dbo].[TestCheck] WITH CHECK ADD CONSTRAINT [CK_TestCheck] CHECK ( ([Code] IS NULL AND [System] IS NULL) --Both null OR ([Code] IS NOT NULL AND ISNULL([System], 0) IN (1, 2, 3)) --Both not null ???? )

Bienvenue dans la merveilleuse logique à trois valeurs de SQL. Comme vous pouvez ou ne pas être au courant, le résultat de toute comparaison standard à null n'est pas TRUE , ou FALSE , mais UNKNOWN .

Dans une clause WHERE , la clause entière doit être évaluée comme TRUE .

Dans une contrainte CHECK , la contrainte entière doit être évaluée comme non FALSE .

Donc nous avons: 

 ([Code] IS NULL AND [System] IS NULL) --Both null OR ([Code] IS NOT NULL AND [System] = 1) --Both not null ????

Qui devient (pour datatables de requête): 

 (FALSE AND TRUE) OR (TRUE AND UNKNOWN)

Et n'importe quel opérateur avec un UNKNOWN d'un côté ou de l'autre évalue comme UNKNOWN , donc le résultat global est UNKNOWN . Ce qui n'est pas FALSE et donc l'évaluation de la contrainte de vérification est réussie.

Si vous voulez que System ne soit pas nul, il est plus clair pour moi si vous ajoutez cela comme une exigence explicite supplémentaire. 

 ([Code] IS NULL AND [System] IS NULL) --Both null OR ([Code] IS NOT NULL AND [System] IS NOT NULL AND [System] = 1) --Both not null ????

Cela peut sembler un peu étrange la façon dont cela est défini, mais cela est cohérent avec le fonctionnement des autres contraintes – par exemple, une contrainte de key étrangère peut avoir des colonnes nullables, et si l'une de ces colonnes est nulle, il n'y a pas être une ligne correspondante dans la table référencée.