Comment créer une contrainte entre deux tables ?
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:
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:
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 ! :D
