Discussion :

[OracleBeginner]

1 Commande possède 1 seul client (obligatoire). Je ne dois avoir aucune commande sans client dans la bd.
1 client possède 1 ou plusieurs commandes. Je ne dois avoir aucun client sans commande dans la bd.

Autrement dit, les 2 cardinalité minimum sont à 1. Pour la commande, il suffit de mettre une contraite not null sur client_id. Mais comment faire pour le client qui doit forcément avoir une commande ?

Commande (id, date_cde, numero, etc...., client_id).
Client (id, nom, prenom, adresse,.....);


Avez-vous une idée comment implémenter cette contrainte pour que le client ait forcément au moins une commande?
J'ai bien pensé à un trigger before INSERT qui contrôlerait que le client ait bien une commande. Le trigger lève une erreur si le client n'a pas de commande. Mais comme le trigger agit après chaque INSERT (et non juste avant le commit), une erreur est tout le temps levée, car je dois commencer avec le client, sinon j'ai une erreur de foreign key non respectée. Une solution simple serait de mettre la cardinalité à 0, mais ici je n'ai pas le droit, je dois la laisser à 1.

Ce code n'est pas valable, car le client n'a aucune commande

INSERT INTO client (id, nom , prenom,....) VALUES (....);
COMMIT;

Ce code est valable, car le client a au moins 1 commande

INSERT INTO client (id, nom , prenom,....) VALUES (10, ....);
INSERT INTO commande (id, date_cde, numero, ..., client_id) VALUES (....,10);
INSERT INTO commande (id, date_cde, numero, ..., client_id) VALUES (....,10);
......
COMMIT;

[aieeeuuuuu]

Bonjour,

Vous pouvez déférer votre contrainte, en spécifiant

Code :

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

1
2
 
INITIALLY DEFERRED DEFERRABLE

ainsi, la validation de la contrainte est effectuée non pas au moment de l'insertion, mais au moment de la validation de la transaction.

[OracleBeginner]

Bonjour,

Vous pouvez déférer votre contrainte, en spécifiant

Code :

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

1
2
 
INITIALLY DEFERRED DEFERRABLE

ainsi, la validation de la contrainte est effectuée non pas au moment de l'insertion, mais au moment de la validation de la transaction.

J'ai réussi à résoudre mon problème. Je vais illustrer mon exemple avec les tables "team" et "player". D'abord, il faut effectivement déférer la contrainte fk pour qu'elle soit checkée au moment du commit (et non à la fin de la ligne). Merci pour le conseil, il m'a bien aidé à résoudre mon problème.

Un team doit avoir 2 (min) ou plusieurs (max) joueurs.
Un joueur doit avoir 1 et 1 seul équipe.

Ci-dessous, un trigger pour la pk de player.

create or replace
TRIGGER TRI_PLAYER_ID
BEFORE INSERT ON player
FOR EACH ROW
WHEN (new.id IS NULL)
BEGIN
SELECT seq_player.NEXTVAL
INTO :new.id
FROM dual;
END;

Ensuite, un trigger qui insère automatiquement la team correcte dans le player. A noter que ce code devrait être mis dans une procédure et dans le trigger précédent.

create or replace
TRIGGER TRI_player_BIR
BEFORE INSERT
ON player
FOR EACH ROW

DECLARE
v_index number;

BEGIN

SELECT seq_team.currval
INTO v_index
FROM dual;

v_index := v_index + 1;

:new.team_id := v_index;

END;

Enfin, le trigger pour la pk sur team et vérifier que la team possède bien 2 players.

create or replace
TRIGGER TRI_TEAM_ID
BEFORE INSERT ON team
FOR EACH ROW
WHEN (new.id IS NULL)

DECLARE
v_nb number;

BEGIN
SELECT seq_team.NEXTVAL
INTO :new.id
FROM dual;

SELECT count(*) INTO v_nb
FROM player
WHERE team_id = :new.id;

IF (v_nb < 2) THEN
Raise_Application_Error (-20001, 'Cardinalite non respectee');
END IF;
END;

Enfin, il faut effectuer la transaction suivante :

INSERT INTO player (nom, prenom) VALUES ('AB', 'GH');
INSERT INTO player (nom, prenom) VALUES ('CD', 'IJ');
INSERT INTO player (nom, prenom) VALUES ('EF', 'KL');
INSERT INTO team (nom) VALUES ('Toulon');
COMMIT;

[mnitu]

ça ne marchera pas!

[OracleBeginner]

ça ne marchera pas!

J'ai testé et cela fonctionne.

Cette transaction est ok :

INSERT INTO player (nom, prenom) VALUES ('AB', 'GH');
INSERT INTO player (nom, prenom) VALUES ('CD', 'IJ');
INSERT INTO player (nom, prenom) VALUES ('EF', 'KL');
INSERT INTO team (nom) VALUES ('Toulon');
COMMIT;

Cette transaction lève l'exception "-20001, 'Cardinalite non respectee'", ce qui est correcte :

INSERT INTO player (nom, prenom) VALUES ('MN', 'OP');
INSERT INTO team (nom) VALUES ('Toulon');
COMMIT;

[mnitu]

Non ça ne marche pas!

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
 
Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 
Connected as mni
 
SQL> 
SQL> Create table team (
  2    id         number(6) primary key,
  3    nom        Varchar2(10)
  4  )
  5  /
 
Table created
SQL> Create table player (
  2    id                number(6) Primary key,
  3    nom               Varchar2(10),
  4    prenom            Varchar2(10),
  5    team_id           references team(id) deferrable initially deferred
  6  )
  7  /
 
Table created
SQL> create sequence seq_team
  2  /
 
Sequence created
SQL> create sequence seq_player
  2  /
 
Sequence created
SQL> create or replace
  2   TRIGGER TRI_PLAYER_ID
  3   BEFORE INSERT ON player
  4   FOR EACH ROW
  5   WHEN (new.id IS NULL)
  6  DECLARE
  7   v_index number;
  8   BEGIN
  9   SELECT seq_player.NEXTVAL
 10   INTO :new.id
 11   FROM dual;
 12   --
 13  SELECT seq_team.currval
 14   INTO v_index
 15   FROM dual;
 16  
 17   v_index := v_index + 1;
 18  
 19   :new.team_id := v_index;
 20  
 21   END;
 22  /
 
Trigger created
SQL> create or replace
  2   TRIGGER TRI_TEAM_ID
  3   BEFORE INSERT ON team
  4   FOR EACH ROW
  5   WHEN (new.id IS NULL)
  6  
  7   DECLARE
  8   v_nb number;
  9  
 10   BEGIN
 11   SELECT seq_team.NEXTVAL
 12   INTO :new.id
 13   FROM dual;
 14  
 15   SELECT count(*) INTO v_nb
 16   FROM player
 17   WHERE team_id = :new.id;
 18  
 19   IF (v_nb < 2) THEN
 20   Raise_Application_Error (-20001, 'Cardinalite non respectee');
 21   END IF;
 22   END;
 23  /
 
Trigger created
 
SQL> 
SQL> INSERT INTO player (nom, prenom) VALUES ('AB', 'GH');
 
INSERT INTO player (nom, prenom) VALUES ('AB', 'GH')
 
ORA-08002: séquence SEQ_TEAM.CURRVAL pas encore définie dans cette session
ORA-06512: à "MNI.TRI_PLAYER_ID", ligne 8
ORA-04088: erreur lors d'exécution du déclencheur 'MNI.TRI_PLAYER_ID'
SQL>  INSERT INTO player (nom, prenom) VALUES ('CD', 'IJ');
 
INSERT INTO player (nom, prenom) VALUES ('CD', 'IJ')
 
ORA-08002: séquence SEQ_TEAM.CURRVAL pas encore définie dans cette session
ORA-06512: à "MNI.TRI_PLAYER_ID", ligne 8
ORA-04088: erreur lors d'exécution du déclencheur 'MNI.TRI_PLAYER_ID'
SQL>  INSERT INTO player (nom, prenom) VALUES ('EF', 'KL');
 
INSERT INTO player (nom, prenom) VALUES ('EF', 'KL')
 
ORA-08002: séquence SEQ_TEAM.CURRVAL pas encore définie dans cette session
ORA-06512: à "MNI.TRI_PLAYER_ID", ligne 8
ORA-04088: erreur lors d'exécution du déclencheur 'MNI.TRI_PLAYER_ID'
SQL>  INSERT INTO team (nom) VALUES ('Toulon');
 
INSERT INTO team (nom) VALUES ('Toulon')
 
ORA-20001: Cardinalite non respectee
ORA-06512: à "MNI.TRI_TEAM_ID", ligne 14
ORA-04088: erreur lors d'exécution du déclencheur 'MNI.TRI_TEAM_ID'
SQL>  COMMIT;
 
Commit complete
 
SQL>

[OracleBeginner]

J'ai compris pourquoi cela fonctionnait dans mes tests. J'avais fait un sequence.nextval auparavant. En effet, l'erreur "ORA-08002: séquence SEQ_TEAM.CURRVAL pas encore définie dans cette session" signifie que la séquence n'a pas encore été initialisée durant cette session. Dès lors, il suffit de faire un seul sequence.nextval pour remédier au problème. C'est la correction que j'ai apportée au trigger ci-dessous.

Si modifiez le trigger comme suit pour traiter l'exception, cela devrait fonctionner :

create or replace
TRIGGER TRI_player_BIR
BEFORE INSERT
ON player
FOR EACH ROW

DECLARE
v_index number;
v_index_init number;

BEGIN

SELECT seq_team.currval
INTO v_index
FROM dual;

v_index := v_index + 1;

:new.team_id := v_index;

EXCEPTION
WHEN OTHERS THEN
IF SQLCODE = -8002 THEN
DBMS_OUTPUT.PUT_LINE( 'Currval not initialisé ') ;

SELECT seq_team.nextval
INTO v_index_init
FROM dual;

v_index_init := v_index_init + 1;
:new.team_id := v_index_init;

END IF;

END;

[mnitu]

...
Si modifiez le trigger comme suit pour traiter l'exception, cela devrait fonctionner :

J'ai bien peur que cella ne suffit pas!