Contraintes complexes ?

[SQLpro]

Bonjour, y a t-il un moyen, sans utiliser de trigger, de réaliser ces contraintes :

1 - les tables en jeu :

Code :

1
2
3
4
5
6
7
8
9
10
CREATE TABLE T_CLIENT_CLI
(CLI_ID          INT NOT NULL PRIMARY KEY,
 CLI_REMISE_MAX  FLOAT);
 
CREATE TABLE T_COMMANDE_CMD
(CMD_ID          INT NOT NULL PRIMARY KEY,
 CLI_ID          INT NOT NULL ,
 CMD_REMISE      NUMBER(5,2) DEFAULT(0) NOT NULL,
 CMD_DATE        DATE DEFAULT SYSDATE,
 CONSTRAINT FK_CMD_CLI FOREIGN KEY (CLI_ID) REFERENCES T_CLIENT_CLI (CLI_ID));

2 - La première contrainte :

Code :

1
2
3
4
5
ALTER TABLE T_COMMANDE_CMD
   ADD CONSTRAINT CK_CMD_REMISE
   CHECK (CMD_REMISE <= (SELECT EXTRACT(YEAR FROM SYSDATE) - EXTRACT(YEAR FROM MIN(CMD_DATE))
                         FROM   T_COMMANDE_CMD C
                         WHERE  C.CLI_ID = CLI_ID));

Erreur rapporté : ORA-02251: sous-interrogation non autorisée ici

3 - La seconde contrainte :

Code :

1
2
3
4
5
ALTER TABLE T_COMMANDE_CMD
   ADD CONSTRAINT CK_CMD_REMISE_MAX
   CHECK (CMD_REMISE <= (SELECT CLI_REMISE_MAX
                         FROM   T_CLIENT_CLI C
                         WHERE  C.CLI_ID = CLI_ID));

Même punition...

Est-ce possible sans trigger, via une UDF (fonction utilisateur) ?

Du genre :

Code :

1
2
3
4
5
6
7
8
9
 
ALTER TABLE T_COMMANDE_CMD
   ADD CONSTRAINT CK_CMD_REMISE_MAX
   CHECK (CMD_REMISE <= F_REMISE_ANCIENNETE(CLI_ID))
 
 
ALTER TABLE T_COMMANDE_CMD
   ADD CONSTRAINT CK_CMD_REMISE_MAX
   CHECK (CMD_REMISE <= F_REMISE_MAX(CLI_ID))

Si oui, comment coder cela ???

A +

[Pomalaix]

C'est vite vu : ce n'est pas possible :
http://oracle.developpez.com/faq/?page=3-1#check

[Waldar]

En évitant les triggers, il faut aller regarder du côté des vues matérialisées (vues indexées en T-SQL) pour suivre ce genre de contrôle.

Pour le second :

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
CREATE MATERIALIZED VIEW MV_CLIENT_COMMANDE_CCM
REFRESH ON COMMIT
AS
SELECT CLI.CLI_ID,
       CLI.CLI_REMISE_MAX,
       CMD.CMD_ID,
       CMD.CMD_REMISE,
       CMD.CMD_DATE
  FROM T_CLIENT_CLI CLI,
       T_COMMANDE_CMD CMD
 WHERE CMD.CLI_ID = CLI.CLI_ID;
-- apparemment ça bug avec la jointure ANSI, il doit chercher à interpréter
-- le mot clef ON de la jointure comme celui de la clause de refresh
 
ALTER MATERIALIZED VIEW MV_CLIENT_COMMANDE_CCM
ADD CONSTRAINT CHK_REMISE_MIN
      CHECK (CMD_REMISE <= CLI_REMISE_MAX);

On teste :

Code :

1
2
3
4
5
6
7
8
INSERT INTO T_CLIENT_CLI (CLI_ID, CLI_REMISE_MAX) VALUES (1, 10);
-- 1 row created.
INSERT INTO T_COMMANDE_CMD (CMD_ID, CLI_ID, CMD_REMISE, CMD_DATE) VALUES (1, 1, 50, SYSDATE);
-- 1 row created.
COMMIT;
--Error at line 5
--ORA-12008: erreur dans le chemin de régénération de la vue matérialisée
--ORA-02290: violation de contraintes (CHK_REMISE_MIN) de vérification

[pachot]

Bonjour,

Quelle que soit la manière de les implémenter, les contraintes qui référencent autre chose que la ligne en cours (c'est à dire qui référencent d'autres tables ou bien d'autres lignes de la même table) posent un gros problème d'isolation des transactions et de lecture concurrente. La plupart du temps, si on veut que ça fonctionne correctement en multi-utilisateurs, c'est à dire sans corrompre les données, on se retrouve à devoir verrouiller toute la table référencée. Et du coup l'appli devient mono-utilisateur.

Ici, pour la première contrainte, ce ne peut pas être une contrainte statique: elle peut être vérifiée au moment de l'insert, mais sera violée si par la suite quelqu'un insère une T_COMMANDE_CMD avec une CMD_DATE plus petite.
La condition de la contrainte est vraie au moment de l'insert, mais fausse par la suite.

Or une contrainte doit être vérifiée tout le temps.

Maintenant, si tu veux implémenter la vérification de manière applicative, ou par trigger, c'est souvent impossible de le faire correctement sans vérrouiller toute la table.

Par exemple, toujours pour la première contrainte, imaginons qu'une transaction concurrente est en train d'insérer une commande avec une CMD_DATE plus petite que toutes les autres, mais n'a pas encore commité sa transaction. Alors ta transaction lorsqu'elle vérifie CK_CMD_REMISE ne voit pas cette ligne (car pas encore commitée) et peut donc mettre une CMD_REMISE supérieure à la valeur CMD_DATE de la session concurrente.
Les 2 sessions pourront faire le commit de leur transaction, mais pourtant la contrainte n'aura jamais été vraie

Pour régler ce cas, il faut empêcher tout insert concurrent sur la table en la verrouillant.
Plutôt impossible dans la vraie vie où plusieurs utilisateurs travaillent en même temps.


La solution contrainte sur vue matérialisée est bonne, même si elle peut être assez coûteuse à chaque DML: elle va vérifier les contraintes au commit.

Donc dans l'exemple ci-dessus, ta session pourra faire son insert, puisque elle ne verra pas l'insert concurrent non commité. C'est par contre la session concurrente qui va planter car elle ne pourra commiter un CMD_DATE plus petit que le CMD_REMISE que tu as mis entre temps.

Reste à savoir si c'est bien ce que l'on veut: toute sa transaction va planter alors que c'est lui qui avait fait l'insert en premier, et que c'était légal à ce moment là. C'est la même philosophie que le verrou optimiste: on espère que personne ne modifie en même temps, on se plante quelque fois, et dans ce cas on doit tout recommencer, mais ça passe la plupart du temps et ça évite de poser des verrous trop longtemps.

Désolé d'avoir été aussi bavard. On voit souvent des applis qui fonctionnent bien avec un seul utilisateur, mais perdent l'intégrité des données dès qu'il y a concurrence d'accés...

Cordialement,
Franck.

[laurentschneider]

Perso, même si je trouve les contraintes sur MVIEWS astucieuses, je suis contre pour les deux raisons ci dessous.

1. les contraintes sont différées au COMMIT et pas au DML
2. si la vue est invalidée, c'est la catastrophe au niveau de l'intégrité des données.

Donc TRIGGER ou contraintes applicatives