Interdire les entrés dupliqués dans une table requête, contrainte, ou autres ?

**sudtek** · 05/05/2012, 23h20

Bonjour,

j'ai réalisé une petite BD de test (voir MLD ci joint) dont j'ai rempli les tables principales et associatives j'ai fait quelques requêtes d'affichage basique qui semblent fonctionner mais je me heurte a un petit pb débutant et ignorant si mon pb est de lié à une requête ou lié à la création de table je poste donc ici dans la section débutant.

Dans la table associative employe_livre ou sont stockés les emprunts de livres par les employés si je fait les requêtes 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
 
--on  insere une ligne dans la table la tables 
-- l'employe 6 | thomas   | volo   à emprunté le livre  28 | 9782266154116 | Le Seigneur des Anneaux, Tome 1 : La communauté de l'Anneau le 03 juin 2012
 insert into employe_livre (IDemploye,IDlivre, date_emprunt) VALUES (6,28,20120503);
 
-- l'employe 7  nicolas  | lepetit  à emprunté le livre  28 | 9782266154116 | 
insert into employe_livre (IDemploye,IDlivre, date_emprunt) VALUES (7,28,20120503);
 
 
SELECT * from employe_livre;
/*mysql> SELECT * from employe_livre;
+-----------+---------+--------------+------------------+
| IDemploye | IDlivre | date_emprunt | date_restitution |
+-----------+---------+--------------+------------------+
|         6 |      28 | 2012-05-03   | NULL             |
|         7 |      28 | 2012-05-03   | NULL             |
+-----------+---------+--------------+------------------+
2 rows in set (0.00 sec)*/

donc pb je peux avoir des entrés dupliqués un même jour pour le même livre pour 2 personnes différentes ... alors qu'il n'y a qu'un exemplaire ...
Donc ce genre de règles / contraintes doit il se régler dans la requête ou est il fixé dans la table ? Quel est le nom de cette "contrainte" ?

Cd sudtek

**CinePhil** · 05/05/2012, 23h43

Effectivement, si un livre ne peut pas être emprunté deux fois le même jour, il faut une contrainte d'unicité (un index de type UNIQUE) sur le couple {id_livre, date_emprunt}.

À la tentative d'insertion, MySQL va renvoyer une erreur de violation de contrainte d'unicité. Il faut que le programme récupère et traite cette erreur pour informer l'utilisateur que l'opération qu'il souhaite faire est impossible.

**sudtek** · 06/05/2012, 00h13

Effectivement, si un livre ne peut pas être emprunté deux fois le même jour, il faut une contrainte d'unicité (un index de type UNIQUE) sur le couple {id_livre, date_emprunt}.

On ne peut pas restituer 2 fois le même livre le même jour donc je dois également avoir une contrainte sur {id_livre, date_restitution} mais le champs peut être null ... donc pour garantir l'unicité je dois prendre toute la ligne idemploye,idlivre,date_emprunt,date_retour ! ? c'est bien un historique ?

comme un employé ne peut pas le réemprunter tant qu'il n'a pas été restitué
les 2 contraintes :
-id_livre, date_emprunt
-idemploye,idlivre,date_emprunt,date_retour
devraient suffirent pour l’employé en cours par contre comment traduire le fait qu'un autre employé ne peut pas l'emprunter tant que le 1er employé ne la pas restitué en mysql ? ce qui revient à dire tant qu'il n'y a pas une date_retour pour une date date emprunt pour un id précis !

**CinePhil** · 06/05/2012, 00h54

Envoyé par sudtek

On ne peut pas restituer 2 fois le même livre le même jour donc je dois également avoir une contrainte sur {id_livre, date_restitution} mais le champs peut être null

C'est à tester mais en principe NULL <> NULL donc la nullabilité de la colonne ne devrait pas gêner la contrainte d'unicité.

comment traduire le fait qu'un autre employé ne peut pas l'emprunter tant que le 1er employé ne la pas restitué en mysql ? ce qui revient à dire tant qu'il n'y a pas une date_retour pour une date date emprunt pour un id précis !

Il faut faire un trigger avant insertion dans la table des emprunts pour vérifier que le livre n'est pas déjà prêté.

Avec un SGBD normatif, une contrainte CHECK devrait suffire mais le mauvais MySQL ne les connaît toujours pas !

**sudtek** · 07/05/2012, 18h21

J'ai pas mal avancé sur les clefs et cela fonctionne donc je passe aux triggers mais là c'est plus complexe... j'ai réussi à trigger un évènement et selon la doc http://dev.mysql.com/doc/refman/5.0/...e-trigger.html on peut trigger 2 évènements

Il ne peut pas y avoir deux déclencheurs pour une même table avec les mêmes configurations de moment et de commande. Par exemple, vous ne pouvez pas avor deux déclencheurs BEFORE UPDATE pour la même table. Mais vous pouvez avoir un déclencheur BEFORE UPDATE et un déclencheur BEFORE INSERT, ou un déclencheur BEFORE UPDATE et un déclencheur AFTER UPDATE.

mais mysql m'envoi une erreur me disant que cela lui va pas les deux évènements ...

version 1 évènement -> ca marche le trigger est ok

Code :

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

1
2
3
4
5
6
7
8
9
delimiter //
CREATE TRIGGER CONTROLE_DATE BEFORE UPDATE ON employe_livre
FOR EACH ROW 
BEGIN
	IF (NEW.date_restitution < OLD.date_emprunt) then 
		SET NEW.date_restitution = NULL;
	END IF;
END//
delimiter ;

mais bon sachant que à l'update ou à l'insert ce sont les mêmes conditions je voudrais bien traiter les 2 cas et la version 5.5.8 semble le gerer mais j'ai pas trouvé d'exemple sur leur site ...

version 2 évènements -> c'est le drame...

Code :

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

1
2
3
4
5
6
7
8
9
delimiter //
CREATE TRIGGER CONTROLE_DATE BEFORE INSERT OR UPDATE ON employe_livre
FOR EACH ROW 
BEGIN
	IF (NEW.date_restitution < OLD.date_emprunt) then 
		SET NEW.date_restitution = NULL;
	END IF;
END//
delimiter ;

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'OR UP

bref c'est clairement l'ordre des directives BEFORE INSERT OR UPDATE qui lui plait pas mais j'ai fait toutes les combos (il me semble ... et toujours rien .

Un piste

?

**CinePhil** · 07/05/2012, 18h31

Le trigger est soit BEFORE INSERT, soit BEFORE UPDATE. Si tu veux les deux, il faut deux triggers.

**sudtek** · 07/05/2012, 18h36

Ok c'est bien ce que je pensai à force de faire des combinaisons il ya une contradiction dans la DOC :

Il ne peut pas y avoir deux déclencheurs pour une même table avec les mêmes configurations de moment et de commande. Par exemple, vous ne pouvez pas avor deux déclencheurs BEFORE UPDATE pour la même table.

Mais vous pouvez avoir un déclencheur BEFORE UPDATE et un déclencheur BEFORE INSERT, ou un déclencheur BEFORE UPDATE et un déclencheur AFTER UPDATE

Merci CinePhil pour avoir lever le doute.

**sudtek** · 08/05/2012, 20h40

au cas ou cela pourrait aider un autre débutant voici les contraintes + triggers que j'ai utilisé pour résoudre mon pb c'est pas orthodoxe (surtout le triger ou des procedures et fonctions seraient les bienvenues) mais ca marche ...

Code :

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

1
2
3
4
5
6
7
8
9
10
/* CONTRAINTES SUR CLEF UNIQUE*/
 
/*CONTRAINTE #1 : Un livre peut être emprunter une seule et unique fois par jour*/
CREATE UNIQUE INDEX EMPRUNTE ON  employe_livre (IDlivre,date_emprunt);
 
/*CONTRAINTE #2 : Un livre peut être restitué qu’une seule et unique fois par jour.*/
CREATE UNIQUE INDEX RESTITUE ON  employe_livre (IDlivre,date_restitution);
 
/*CONTRAINTE #3 : On désire avoir un historique des emprunts et éviter les doublons.*/
CREATE UNIQUE INDEX HISTORIQUE ON employe_livre (IDemploye,IDlivre,date_emprunt,date_restitution);

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
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
/* LES TRIGGER */
/*TRIGGER = déclencheur ou valideur avant/apres maj/insertion/supression d'une donnée*/
 
/*========== TRIGGER ==========*/
 
/*Documentation generale sur les triggers
<a href="http://dev.mysql.com/doc/refman/5.0/fr/using-triggers.html" target="_blank">http://dev.mysql.com/doc/refman/5.0/...-triggers.html</a>
<a href="http://www.lesite.com/tutoriel-3-227634-triggers-et-vues-materialisees-mysql.html#ss_part_4" target="_blank">http://www.lesite.com/tutoriel-3-227...html#ss_part_4</a>
<a href="http://dev.mysql.com/doc/refman/5.0/en/show-triggers.html*/" target="_blank">http://dev.mysql.com/doc/refman/5.0/...riggers.html*/</a>
 
/* Operations sur les TRIGGERS
SHOW TRIGGERS; -- pour affichier la liste des triggers; 
DROP trigger nomDuTrigger; -- on suprimme un trigger; 
CREATE
    [DEFINER = { user | CURRENT_USER }] -- si s'applique à un utilisateur particulier;
    TRIGGER trigger_name trigger_time trigger_event -- nom_du_triger  moment(avant_OU_aprés) evenementdeclencheur(màj, ou insertion ou supression);
    ON tbl_name FOR EACH ROW trigger_body
 
	delimiter //	
CREATE TRIGGER nomDuTrigger BEFORE UPDATE ON nomDeLaTable
FOR EACH ROW -- commande à exécuter à chaque fois que le déclencheur s'active, ce qui arrive à dès qu'une ligne est insérée;
BEGIN
IF (<CONDITION>)then
	SIGNAL SQLSTATE '70000';
end IF;
END;
delimiter ; -- ne pas suprimer l'espace du delimiteur si non c'est // qui est d'actualité !;
 
 
delimiter //
CREATE TRIGGER CONTROLE_DATE BEFORE UPDATE ON employe_livre
FOR EACH ROW 
BEGIN
	IF (NEW.date_restitution < OLD.date_emprunt) then 
		SET NEW.date_restitution = NULL;
	END IF;
END//
delimiter ; -- ne pas suprimer l'espace du delimiteur si non c'est // qui est d'actualité !;
 
 
 
*/
 
/* NOTE : on ne peut gerer deux evenements dans le même trigger sinon cela marche pas --> il faut faire deux triggers !
delimiter //
CREATE TRIGGER CONTROLE_DATE BEFORE INSERT OR UPDATE ON employe_livre
FOR EACH ROW 
BEGIN
	IF (NEW.date_restitution < OLD.date_emprunt) then 
		SET NEW.date_restitution = NULL;
	END IF;
END//
delimiter ;*/
/* NOTE : On ne peut créer qu'un trigger du même type BEFORE UPDATE, BEFORE INSER ...--> il faut coller le code de test dasn le triger unique */
/* NOTE : les commentaires dans la partie procedurale des triggers se font avec un # */
 
 
/*=========== TRIGGER #1 BEFORE_UPDATE : =========*/
DELIMITER //
DROP TRIGGER IF EXISTS CONTROLE_DATE_EMPRUNT_BEFORE_UPDATE
//
CREATE TRIGGER CONTROLE_DATE_EMPRUNT_BEFORE_UPDATE BEFORE UPDATE ON employe_livre
FOR EACH ROW 
BEGIN
	#----------------------------------------------------------------------------------------------------
	#On ne peut maj une NOUVELLE date_restitution si elle est antérieure la NOUVELLE date_emprunt
	IF (NEW.date_restitution < NEW.date_emprunt) THEN 
		SET NEW.date_restitution = NULL; #on fixe a NULL
	END IF;
	#----------------------------------------------------------------------------------------------------
	#La maj de la NOUVELLE date_emprunt ne peut etre nulle ni égale à = 0000-00-00 ou antérieure à la date du jour
	IF ((NEW.date_emprunt = NULL) OR (NEW.date_emprunt = 0000-00-00) OR (NEW.date_emprunt < (CURDATE()))) THEN 
		SET NEW.date_emprunt = CURDATE(); #on enregistre la date du jour
	END IF;
	#----------------------------------------------------------------------------------------------------
END//
DELIMITER ; -- NE PAS SUPRIMMER L'ESPACE DU DELIMITEUR SI NON C'EST // QUI RESTE LE DÉLIMITEUR POUR MYSQL !;
 
/*TRIGGER #2 BEFORE_INSERT :*/
DELIMITER //
DROP TRIGGER IF EXISTS CONTROLE_DATE_EMPRUNT_BEFORE_INSERT
//
CREATE TRIGGER CONTROLE_DATE_EMPRUNT_BEFORE_INSERT BEFORE INSERT ON employe_livre
FOR EACH ROW 
BEGIN
	DECLARE cpt TINYINT(1);
	#MARCHE PAS : #DECLARE valeurNouveauIDlivre TINYINT(1);
	#----------------------------------------------------------------------------------------------------
	#On ne peut inserer une NOUVELLE date_restitution si elle est antérieure la NOUVELLE date_emprunt
	IF (NEW.date_restitution < NEW.date_emprunt) THEN 
		SET NEW.date_restitution = NULL; #on fixe a NULL
	END IF;
	#----------------------------------------------------------------------------------------------------
	#La NOUVELLE date_emprunt ne peut etre nulle, ni égale à = 0000-00-00 ou antérieure à la date du jour
	IF ((NEW.date_emprunt = NULL) OR (NEW.date_emprunt = 0000-00-00) OR (NEW.date_emprunt < (CURDATE()))) THEN 
		SET NEW.date_emprunt = CURDATE(); #on enregistre la date du jour
	END IF;
	#----------------------------------------------------------------------------------------------------
	#Un livre ne peut être emprunté que s’il a été restitué.
	#on evalue si le livre ne fait pas partie de ceux qui sont empruntés
	#MARCHE PAS : #SET valeurNouveauIDlivre = SELECT NEW.IDlivre IN (SELECT * FROM liste_ID_Livres_Emprunte);
	#MARCHE PAS : #SET valeurNouveauIDlivre = select COUNT(*) from employe_livre where IDlivre = NEW.IDlivre and date_restitution IS NULL;
	select COUNT(*) INTO cpt from employe_livre where employe_livre.IDlivre = NEW.IDlivre and employe_livre.date_restitution IS NULL;
	IF (cpt >= 1) THEN
		SET NEW.IDlivre = NULL; #nul n est pas autorisé par les clefs du coup cela avorte le insert, c est moche mais ca marche 
	END IF;
	#----------------------------------------------------------------------------------------------------
	#Un livre ne peut être restitué s’il n’a pas été emprunté --> n'existe pas dans le cas màj.
	#Le fait que  iIDlivre`,`IDemploye`,`date_emprunt` soit non null cela interdit de créer une date de restitution fantomme qui se ballade seule
END//
DELIMITER ; -- NE PAS SUPRIMMER L'ESPACE DU DELIMITEUR SI NON C'EST // QUI RESTE LE DÉLIMITEUR POUR MYSQL !;
 
/* DOC à lire :
May a trigger stop an INSERT ? --> <a href="http://forums.mysql.com/read.php?99,126894,216870#msg-216870" target="_blank">http://forums.mysql.com/read.php?99,...870#msg-216870</a>
Trigger "before insert" --> <a href="http://www.developpez.net/forums/d751141/bases-donnees/firebird/trigger-before-insert/" target="_blank">http://www.developpez.net/forums/d75...before-insert/</a>
*/
 
/*mysql>mysql> show triggers ;
+-------------------------------------+--------+---------------+------------------------------------------------------------------------------------------------------------------
-----------------------------------------------------+--------+---------+----------+--------------------------+----------------------+----------------------+--------------------+
| Trigger                             | Event  | Table         | Statement
                                                     | Timing | Created | sql_mode | Definer                  | character_set_client | collation_connection | Database Collation |
+-------------------------------------+--------+---------------+------------------------------------------------------------------------------------------------------------------
-----------------------------------------------------+--------+---------+----------+--------------------------+----------------------+----------------------+--------------------+
| CONTROLE_DATE_EMPRUNT_BEFORE_INSERT | INSERT | employe_livre | BEGIN
        DECLARE cpt TINYINT(1);
 
 
 
        IF (NEW.date_restitution < NEW.date_emprunt) THEN
                SET NEW.date_restitution = NULL;
        END IF;
 
 
        IF ((NEW.date_emprunt = NULL) OR (NEW.date_emprunt = 0000-00-00) OR (NEW.date_emprunt < (CURDATE()))) THEN
                SET NEW.date_emprunt = CURDATE();
        END IF;
 
 
 
 
 
        select COUNT(*) INTO cpt from employe_livre where employe_livre.IDlivre = NEW.IDlivre and employe_livre.date_restitution IS NULL;
        IF (cpt >= 1) THEN
                SET NEW.IDlivre = NULL;
        END IF;
 
 
 
END | BEFORE | NULL    |          | administrateur@localhost | latin1               | latin1_swedish_ci    | utf8_general_ci    |
| CONTROLE_DATE_EMPRUNT_BEFORE_UPDATE | UPDATE | employe_livre | BEGIN
 
 
        IF (NEW.date_restitution < NEW.date_emprunt) THEN
                SET NEW.date_restitution = NULL;
        END IF;
 
 
        IF ((NEW.date_emprunt = NULL) OR (NEW.date_emprunt = 0000-00-00) OR (NEW.date_emprunt < (CURDATE()))) THEN
                SET NEW.date_emprunt = CURDATE();
        END IF;
 
END                                                                                                                                                                               
+-------------------------------------+--------+---------------+------------------------------------------------------------------------------------------------------------------
-----------------------------------------------------+--------+---------+----------+--------------------------+----------------------+----------------------+--------------------+
2 rows in set (0.03 sec)
 
 
*/

Merci pour les infos cinePhil !

cd SUDTEK

Interdire les entrés dupliqués dans une table requête, contrainte, ou autres ?

MySQL

Discussions similaires

Partager

Partager