Discussion :

[Invité]

Bonjours à tous !

J'ai un problème et sa fait trop longtemps que je cherche la solution alors poste ici en espérant trouver de l'aide.

Je cherche à créer une contrainte entre deux tables (Emprunts et Stocks) dans ma base de données Postgresql.
Dans la table Stocks il y a une colonne qauntité.
Dans la table Emprunts, une ligne correspond à l'emprunt d'un objet du stock. Si le champ DateRetour est NULL c'est que l'objet est en ce moment emprunté, sinon c'est qu'il a été rendu à la date indiquée.
Le champ quantité reste fixe. Le nombre d'exemplaire d'objet disponible (non emprunté en ce moment) est calculé avec la différence de la quantité avec le nombre de lignes de cette objet dans la table Emprunts.
Donc la contrainte que je veux exprimer est la suivante : la quantité d'un objet dans la table Stocks doit être supérieur ou égal aux nombre de lignes concernant ce même objet dans la table Emprunts.

Voici la structure des deux tables :

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
CREATE TABLE Stocks (
	IdFilm		INTEGER	REFERENCES Films(IdFilm),
	IdFormat	INTEGER	REFERENCES Formats(IdFormat),
	Quantite	INTEGER	CHECK (Quantite > 0),
	PRIMARY KEY (IdFilm, IdFormat)
);
 
CREATE TABLE Emprunts (
	IdEmp		INTEGER	PRIMARY KEY,
	IdCompte	INTEGER	NOT NULL REFERENCES Comptes(IdCompte),
	IdFilm		INTEGER	NOT NULL,
	IdFormat	INTEGER	NOT NULL,
	DateEmp		date	NOT NULL DEFAULT current_date,
	DateRetour	date	CHECK (DateEmp <= DateRetour),
	DureeMax	INTEGER	NOT NULL DEFAULT 5 CHECK (dureemax > 0),
	FOREIGN KEY (IdFilm, IdFormat) REFERENCES Stocks(IdFilm, IdFormat)
);

Il faut savoir que les assertions ne sont pas implémenté et que le langage plpgsql n'est pas présent.

J'ai essayé de la manière suivante :

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
23
24
25
26
-- On ne peut pas faire de sous-requête dans un CHECK donc on va utiliser deux fonctions.
 
CREATE FUNCTION nb_emprunts(INTEGER, INTEGER) RETURNS BIGINT
AS '
SELECT NbEmp
FROM ( SELECT Emprunts.IdFilm, Emprunts.IdFormat, Count(*) AS NbEmp
       FROM Emprunts
       WHERE DateRetour IS NULL AND Emprunts.IdFilm = $1 AND Emprunts.IdFormat = $2
       GROUP BY Emprunts.IdFilm, Emprunts.IdFormat ) AS R;
' LANGUAGE SQL;
 
CREATE FUNCTION quantite(INTEGER, INTEGER) RETURNS INTEGER
AS '
SELECT Quantite
FROM Stocks
WHERE IdFilm = $1 AND IdFormat = $2;
' LANGUAGE SQL;
 
ALTER TABLE Stocks ADD CONSTRAINT quantite_superieur_aux_emprunts
CHECK ( Quantite >= nb_emprunts(IdFilm, IdFormat) );
 
ALTER TABLE Emprunts ADD CONSTRAINT quantite_superieur_aux_emprunts
CHECK ( quantite(IdFilm, IdFormat) >= nb_emprunts(IdFilm, IdFormat) );
 
-- Problème : la vérification se fait avant l'insertion
--            donc on peut insérer un élément violant la contrainte.

Mais cette solution ne marche pas. On peut ajouter un emprunt de trop. Je pense que c'est parce que les fonctions exécutent leurs requêtes avant l'insertion de la nouvelle ligne.

Je me suis demander si je pouvais faire ça avec un trigger qui, après une insertion, vérifie la contrainte et lève une exception pour ne pas valider l'insertion. Mais je ne vois pas comment faire.

Avez-vous une solution à me proposer ?
Si oui, je vous en remercie d'avance !

[SQLpro]

Votre contrainte :

la quantité d'un objet dans la table Stocks doit être supérieur ou égal aux nombre de lignes concernant ce même objet dans la table Emprunts.

Est mal exprimée. Ce serait plutôt l'inverse qu'il faudrait dire :
Le nombre d'emprunts ne doit pas dépasser la quantité en stocke pour un même objet.
Traduit en requête :

Code :

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

1
2
3
4
5
6
7
IF EXISTS(SELECT 1
          FROM   Emprunts AS E
                 INNER JOIN Stocks AS S
                       ON E.IdFilm = S.IdFilm
          GROUP  BY S.IdFilm
          WHERE  COUNT(*) > S.Quantite)
   ROLLBACK

Et ceci mis dans un déclencheur de type AFTER INSERT sur table Emprunts.
Mais je n'ai pas trouvé dans PostGreSQL comment annuler une transaction au sein du trigger !

A +

[rbaraer]

Les triggers ne peuvent être écrits qu'avec un langage procédural installé.

Ne pouvez-vous pas installer (ou le demander) PL/PgSQL ? L'idée ensuite est d'utiliser un trigger comme proposé par SQLPro et de lever une exception plutôt que de faire le ROLLBACK directement : il sera fait au-dessus, par le programme qui essaie d'effectuer l'emprunt.

Exemple d'exception en PL/PgSQL :

Code :

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

RAISE EXCEPTION 'Plus de stock disponible';

Sinon avec votre solution (au passage je ne savais pas que l'on pouvait utiliser une contrainte CHECK qui effectue une requête, ça peut être pratique), si vous effectuez derrière un update bidon sur la table, qui sur la ligne insérée remplace l'idfilm par le même par exemple, la contrainte CHECK devrait être à nouveau testée et cette fois-ci échouer, en toute logique. Ce n'est pas génial mais bon .

[Invité]

Même le IF EXISTS ne marche pas.

Je vais voir si je peux demander d'ajouter le langage PL/PgSQL.

Même si je n'ai pas ces outils, il n'y a pas de solution ?

[rbaraer]

Je vous ai donné une solution pas terrible mais qui devrait marcher :

Sinon avec votre solution (au passage je ne savais pas que l'on pouvait utiliser une contrainte CHECK qui effectue une requête, ça peut être pratique), si vous effectuez derrière un update bidon sur la table, qui sur la ligne insérée remplace l'idfilm par le même par exemple, la contrainte CHECK devrait être à nouveau testée et cette fois-ci échouer, en toute logique. Ce n'est pas génial mais bon .

Avez-vous pu installer PL/PgSQL ?