Discussion :

[Kropernic]

Hello,

Une rapide question au sujet des contraintes CHECK car un doute m'habite.

Est-ce que le check est fait avant ou après l'insertion de la/des ligne/s ?

Un cas concret...
Soit la table suivante :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
CREATE TABLE T(
    ID INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
    CODE SMALLINT NOT NULL,
    STARTDATE DATE NOT NULL,
    ENDDATE DATE NOT NULL)
GO
ALTER TABLE T WITH CHECK ADD CONSTRAINT AK_T UNIQUE (CODE, STARTDATE)
GO

Jusque là, pas de souci. Maintenant, en plus de cette contrainte d'unicité, je voudrais ajouter une contrainte check qui interdit le chevauchement de période pour un même code.
Je crée donc une fonction pour vérifier cela qui donne ceci :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
CREATE FUNCTION S_HP.UF_CHECK_OVERLAPPING_CODE(@CODE SMALLINT, @START DATE, @END DATE) RETURNS BIT
AS
BEGIN
    DECLARE @RESULT BIT
 
    IF EXISTS (    SELECT    1
                FROM    T
                WHERE    CODE = @CODE
                    AND dbo.OVERLAPS(@START, @END, STARTDATE, ENDDATE) = 1)
        SET @RESULT = 1
    ELSE
        SET @RESULT = 0
    RETURN @RESULT
END
GO

Maintenant, si le check se fait après l'insertion, le résultat sera toujours 1 et il faut que j'ajoute une branche dans la clause WHERE pour exclure la ligne en cours de check sur base de son ID. Je pourrais le faire d'office et ne pas me poser de question mais j'aime bien faire les choses correctement.

Merci d'avance.

[hmira]

...
Maintenant, si le check se fait après l'insertion, le résultat sera toujours 1 et il faut que j'ajoute une branche dans la clause WHERE pour exclure la ligne en cours de check sur base de son ID. Je pourrais le faire d'office et ne pas me poser de question mais j'aime bien faire les choses correctement.

Merci d'avance.

Une contrainte CHECK retourne True (Vraie) lorsque la condition qu'elle vérifie n'est pas False et ce pour n'importe quelle ligne de la table. Si une table venant d'être créée ne comporte aucune ligne, les éventuelles contraintes CHECK sur cette table sont considérées comme valides. Cette situation peut produire des résultats inattendus, comme l'illustre l'exemple suivant.

Code SQL :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
CREATE TABLE CheckTbl (col1 int, col2 int);
GO
CREATE FUNCTION CheckFnctn()
RETURNS int
AS 
BEGIN
   DECLARE @retval int
   SELECT @retval = COUNT(*) FROM CheckTbl
   RETURN @retval
END;
GO
ALTER TABLE CheckTbl
ADD CONSTRAINT chkRowCount CHECK (dbo.CheckFnctn() >= 1 );
GO

- La contrainte CHECK ajoutée spécifie que la table CheckTbl doit contenir au moins une ligne. Toutefois, comme la table ne contient aucune ligne par rapport à laquelle vérifier la condition de cette contrainte, l'instruction ALTER TABLE réussit.

Il suffit ensuite de changer la valeur 1 par une valeur strictement supérieur à 1 (exemple (dbo.CheckFnctn() >= 2 ) et il ne sera plus possible d'insérer aucune ligne dans la table CheckTbl , dès lors qu'on part d'une table vide !!!!

- Par ailleurs, les contraintes CHECK ne sont pas validées pendant les instructions DELETE. Par conséquent, l'exécution d'instructions DELETE sur des tables avec certains types de contraintes de vérification peuvent produire des résultats inattendus. Imaginons, par exemple, les instructions suivantes exécutées sur la table CheckTbl.

Code SQL :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
INSERT INTO CheckTbl VALUES (10, 10)
GO
DELETE CheckTbl WHERE col1 = 10;

L'instruction DELETE réussit, même si la contrainte CHECK spécifie que la table CheckTbl doit comporter au moins 1 ligne.

Pour revenir à votre question initiale, il faut considérer que le contrainte doit être vérifiée à tout instant AVANT et APRES Insertion. Dans votre cas, partant du principe que la contrainte était vérifiée AVANT insertion, il faut que la dite contrainte soit et reste vérifiée également APRES insertion.
Il vous faudra donc impérativement rajouter dans la clause WHERE ID <> @Id pour exclure la ligne en cours.

A+

[Kropernic]

Merci pour cette réponse détaillée