Discussion :

[miniBob]

Bonjour,

je suis en train de tester des triggers que je dois présenté demain à l'oral de PTI pour mon BTS, ces PTI fonctionnait très bien lors des nombreux tests que j'ai pu faire sur la machine de mon lycée, mais ils me retournent une erreur sur le postgresql que j'ai pu installé sur mon ordinateur portable.

L'erreur est la suivante :

ERROR : duplicate key value violates unique

constraint...

je vous envoie un screen :



Lors de mon test j'ai essayé de supprimer une commande dans la table MTX_COMMANDE, ce qui doit avoir pour effet de supprimer les lignes de cette commande dans la table MTX_LIGNE_COMMANDE...

si quelqu'un pouvait m'aider je lui en serais grandement reconnaissant
je vous envoie au cas ou mon fichier sql.

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
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
drop database if exists mystix;
create database mystix;
\c mystix;
 
drop table if exists MTX_LIGNE_COMMANDE, MTX_SHOP_PHOTO, MTX_SHOP_ARTICLE, MTX_COMMANDE, MTX_MEMBER, MTX_SHOP_MARQUE, MTX_SHOP_CAT;
 
create table MTX_SHOP_CAT (shop_cat_num int primary key not null, shop_cat_libelle varchar(50) not null, shop_cat_image varchar(50) not null, shop_cat_order int not null);
 
create table MTX_SHOP_MARQUE (shop_marque_num int primary key not null, shop_marque_libelle varchar(50) not null, shop_marque_site varchar(50), shop_marque_logo varchar(50));
 
create table MTX_MEMBER (userid int primary key not null, username varchar(50) not null, password varchar(50) not null, email varchar(50) not null, nom varchar(50) not null, prenom varchar(50) not null, naissance varchar(10) not null, adresse varchar(50), ville varchar(50), CP varchar(5), pays varchar(50));
 
create table MTX_COMMANDE (commande_num int primary key not null, commande_fraisport float not null, commande_member int references MTX_MEMBER(userid) not null);
 
create table MTX_SHOP_ARTICLE (shop_article_num int primary key not null, shop_article_libelle varchar(50) not null, shop_article_description varchar(500), shop_article_prix float not null, shop_article_marque int references MTX_SHOP_MARQUE(shop_marque_num) not null, shop_article_cat int references MTX_SHOP_CAT(shop_cat_num) not null, shop_article_stock int not null, shop_article_miniature varchar(50) not null);
 
create table MTX_SHOP_PHOTO (shop_photo_num int primary key not null, shop_shop_photo varchar(50) not null, shop_photo_art int references MTX_SHOP_ARTICLE(shop_article_num) not null);
 
create table MTX_LIGNE_COMMANDE (ligne_num int not null, ligne_commande int references MTX_COMMANDE(commande_num) not null, ligne_article int references MTX_SHOP_ARTICLE(shop_article_num), ligne_prix float not null, primary key (ligne_num, ligne_commande));
 
insert into MTX_SHOP_CAT values (1,"Souris","souris.jpg",1);
insert into MTX_SHOP_CAT values (2,"Clavier","clavier.jpg",2);
insert into MTX_SHOP_CAT values (3,"Casque","casque.jpg",3);
 
insert into MTX_SHOP_MARQUE values (1,"Logitech","www.logitech.se","Logitech.jpg");
insert into MTX_SHOP_MARQUE values (2,"Razer","www.razer.com","Razer.jpg");
insert into MTX_SHOP_MARQUE values (3,"Roccat","www.roccat.org","roccat.jpg");
insert into MTX_SHOP_MARQUE values (4,"Icemat","www.icemat.fr","icemat.jpg");
 
insert into MTX_MEMBER values (1,"iBob","azerty","iBob@mysTix.fr","Lelong","Florian","15/06/1987","21 rue Tony Lainé","Poitiers","86000","France");
insert into MTX_MEMBER values (2,"Guigui","azerty","guigui@mystix.fr","Faucon","Guillaume","28/10/1987","11 rue de la Ganterie","Poitiers","86000","France");
insert into MTX_MEMBER values (3,"Pauleta","azerty","pauleta@psg.fr","Pauleta","Pedro","25/01/1974","3 rue des Bosquets","Poitiers","86000","France");
 
insert into MTX_COMMANDE values (1,18.50,1);
insert into MTX_COMMANDE values (2,8.50,1);
 
insert into MTX_SHOP_ARTICLE values (1,"RAZER Lycosa","Clavier de la marque Razer",79.90,2,2,9,"lycosa.jpg");
insert into MTX_SHOP_ARTICLE values (2,"Razer Copperhead Tempest","Souris pour joueurs",70,2,1,5,"copperhead.jpg");
insert into MTX_SHOP_ARTICLE values (3,"Icemat Siberia Blanc","Casque de haute qualité",90,4,3,0,"icematsiberia.jpg");
 
insert into MTX_LIGNE_COMMANDE values (1,1,1,79.90);
insert into MTX_LIGNE_COMMANDE values (2,1,2,80);
insert into MTX_LIGNE_COMMANDE values (3,1,3,65);
insert into MTX_LIGNE_COMMANDE values (1,2,2,65);
 
-- on créé la fonction supprime_commande
create or replace function supprime_commande()returns trigger as $$
 
-- on déclare les variables
declare
 
-- le corps du code
begin
 delete from MTX_LIGNE_COMMANDE where ligne_commande = old.commande_num;
 
return old;
end;
$$ language 'plpgsql';
 
 
create trigger trigger1 before
delete on MTX_COMMANDE
for each row 
execute procedure supprime_commande();
 
 
-- on créé la fonction calcul_ligne
create or replace function calcul_ligne()returns trigger as $$
 
-- on déclare les variables
declare
 
-- le corps du code
begin
	update MTX_LIGNE_COMMANDE set ligne_num = ligne_num - 1 where ligne_num > old.ligne_num;
 
return old;
end;
$$ language 'plpgsql';
 
 
create trigger trigger2 before
delete on MTX_LIGNE_COMMANDE
for each row 
execute procedure calcul_ligne();

[estofilo]

Je crois que tu te heurtes à une faiblesse de postgresql avec les contraintes uniques, à savoir qu'à l'intérieur même de l'exécution d'un UDPATE, s'il y a une étape intermédiaire pendant laquelle il y a violation de l'unicité, alors une erreur est levée immédiatement.
Normalement il faudrait que l'erreur soit déclenchée uniquement s'il y violation une fois que l'UPDATE est fini.
Ce problème arrive typiquement quand on change dans un UPDATE la valeur d'une clef primaire de plusieurs lignes en même temps.
Personnellement je ne connais pas de solution propre.

Sinon du point de vue de la conception, ce n'est pas terrible d'avoir ce numéro de ligne de commande qui est recalculé quand on efface une commande. En général on ne "bouche pas les trous" en cas d'effacement, ça complique les choses et ça ne sert à rien, sauf évidemment si c'est exigé par le fonctionnel.

[SQLpro]

La solution est de gérer une transaction et si besoin est de paramétrer la déférabilité des contraintes comme indiqué dans mon livre sur SQL : http://sqlpro.developpez.com/images/...QL2008_400.jpg

En complément vous pouvez lire le blog que j'ai fait sur ce sujet :
http://blog.developpez.com/sqlpro/p6...-deferabilite/

A +

[estofilo]

En l'occurrence ça ne résoudra pas le problème parce qu'avec postgresql il n'y a pas de déférabilité des contraintes d'unicité, uniquement des contraintes d'intégrité référentielle.
Et c'est même pire que ça puisque le problème se produit non pas après l'UPDATE mais pendant l'UPDATE, en violation de SQL-92 qui dit clairement qu'en mode immédiat le test doit être à la fin de l'instruction SQL.

[miniBob]

bon je suis passé à l'oral ce matin, et avec la chance unique que j'ai je suis tombé sur cette PTI :p

Bon j'ai présenté mes triggers qui ne fonctionnaient pas mais le jury n'a pas dit grand chose et à même trouvé mon erreur qui se trouvait à la fin du deuxième triggers.

Code :

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

1
2
3
4
CREATE TRIGGER trigger2 before
DELETE ON MTX_LIGNE_COMMANDE
FOR each row 
execute procedure calcul_ligne();

il suffisait de préciser que la fonction devait être exécutée après la suppression et non pas avant.

Code :

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

1
2
3
4
CREATE TRIGGER trigger2 after
DELETE ON MTX_LIGNE_COMMANDE
FOR each row 
execute procedure calcul_ligne();

[estofilo]

Effectivement le trigger doit être du type AFTER DELETE mais c'est un train qui en cache un autre. Voici un exemple très simple qui montre le problème dont je parle, reproductible en version 8.3:

Code :

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

1
2
3
4
5
6
 
create table cmd(i int primary key);
insert into cmd values(1);
insert into cmd values(2);
update cmd set i=i+1;
ERROR:  duplicate key value violates unique constraint "cmd_pkey"

Normalement cet UPDATE devrait transformer 1 en 2 et 2 en 3 sans provoquer aucune erreur mais hélas PG ne sait pas le faire. Il remplace 1 par 2 et échoue parce que 2 existe déjà en ignorant le fait que 2 va être remplacé par 3 et qu'au final il n'y aura donc pas de pb d'unicité.