Discussion :

[MagicTux]

Bonsoir à tous,

voici mon souci : Je cherche à empêcher qu'un numéro de facture soit utilisé plus d'une fois pour une même année.

Mon code est donc composé d'une fonction et d'une contrainte check:

FONCTION :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
CREATE FUNCTION checkFNumber(@fnumber varchar(8000), @date datetime) RETURNS INT
AS
BEGIN
	DECLARE @dp int
	SET @dp = DATEPART(year, @date)
	RETURN
	CASE
		WHEN @fnumber NOT IN (SELECT DISTINCT numero FROM Facture WHERE DATEPART(Year, date_facture) = @dp)
		THEN 1
		ELSE 0
	END
END

CONTRAINTE:

Code :

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

ALTER TABLE Facture ADD CONSTRAINT UniqueFNumberConstraint CHECK (dbo.checkFNumber(numero, date_facture) = 1)

Je tente d'insérer une facture :

Code :

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

1
2
 
INSERT INTO Facture(numero, date_facture) VALUES('2', GETDATE());

Erreur de SQL Server :

Msg*547, Niveau*16, État*0, Ligne*1
L'instruction INSERT est en conflit avec la contrainte CHECK "UniqueFNumberConstraint". Le conflit s'est produit dans la base de données "automobile", table "dbo.Facture".
L'instruction a été arrêtée.

Il n'y a pourtant aucune données dans la table Facture au moment de l'insertion... comment résoudre ce problème de contrainte?

Merci d'avance

[elsuket]

Bonjour,

Il aurait fallu remplacer par < 2, mais le mieux reste de mettre une contrainte d'unicité, qui est faite exactement pour cela.
Vous aurez donc besoin d'une colonne calculée qui vous retourne l'année :

Code :

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

1
2
ALTER TABLE dbo.Facture 
ADD annee_facture AS (YEAR(date_facture))

Et la contrainte d'unicité devient alors :

Code :

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

1
2
ALTER TABLE dbo.Facture 
ADD CONSTRAINT UQ_Facture__annee_facture__numero UNIQUE (annee_facture, numero)

Ce sera en outre bien moins coûteux que d'appeler la fonction

@++

[MagicTux]

Il aurait fallu remplacer par < 2

Que veux tu dire par ça?

En tout cas merci, cette autre solution fonctionne à merveille.

[elsuket]

En fait, < 2 ne fonctionnera pas non plus.
Donc en supposant que les contraintes d'unicité n'existent pas, il aurait mieux valu écrire :

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
16
17
18
19
20
21
22
CREATE FUNCTION checkFNumber
	(
		@fnumber varchar(8000)
		, @date datetime
	) 
RETURNS bit
AS
BEGIN
	DECLARE @result bit
	SET @result = 1
 
	IF EXISTS
	(
		SELECT	*
		FROM	dbo.Facture
		WHERE	date_facture BETWEEN CAST(YEAR(@date) + '0101' AS datetime) AND CAST(YEAR(@date) + '1231' AS datetime)
		AND	numero = @fnumber
	)
	SET @result = 0
 
	RETURN @result
END

Et votre contrainte aurait alors été :

Code :

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

1
2
3
ALTER TABLE dbo.Facture
ADD CONSTRAINT UniqueFNumberConstraint
CHECK (dbo.checkFNumber(numero, date_facture) = 1)

C'est à dire inchangée, mais le comportement correct

@++