Relation réflexive avec mise à jour/suppression en cascade

Bonsoir,

aujourd'hui, j'ai eu envie de mettre à l'épreuve mon SGBD : lui serait-il possible de gérer la mise à jour/suppression en cascade dans le cadre d'une relation réflexive ? Je m'explique :

Soit la table test constituée de 2 champs, pk et fk :

Code:

---

1 2 3 4 5 6

pk fk ----- 1 1 2 1 3 2

---

Je souhaite établir une relation entre le champ fk (clef étrangère) et le champ pk (clef primaire) afin que la suppression/mise à jour d'un enregistrement entraîne la suppression/mise à jour des enregistrements enfants.
En l'occurrence, la suppression de l'enregistrement 1/1 entraînerait donc celle de l'enregistrement 2/1... qui entraînerait elle-même celle de l'enregistrement 3/2.
Le but n'est pas de gérer cela avec un trigger, mais de savoir si le SGBD est en mesure de le gérer dans le cadre d'une simple relation avec cascade.

J'ai donc tenté d'exécuter l'instruction de création de table suivante sous SQL Server 2000 :

Code:

---

1 2 3 4 5 6

CREATE TABLE test ( pk NUMERIC, fk NUMERIC, CONSTRAINT c1 PRIMARY KEY (pk), CONSTRAINT c2 FOREIGN KEY (fk) REFERENCES test (pk) ON UPDATE CASCADE ON DELETE CASCADE);

---

Et SQL Server 2000 de me répondre :

Citation:

---

L'introduction d'une contrainte de clé étrangère 'c2' dans la table 'TEST' peut provoquer des cycles ou des accès en cascade multiples. Spécifiez ON DELETE NO ACTION ou ON UPDATE NO ACTION ou modifiez les autres contraintes de clés étrangères

---

La réponse n'est pas celle que j'espérais, mais elle a le mérite d'être claire.

Me demandant si un autre SGBD serait en mesure de gérer ce type de relation, j'ai essayé de la mettre en place sous Access. Access me laisse bien mettre en place la relation, mais lorsque j'essaie de supprimer un enregistrement parent, j'essuie le refus suivant :

Citation:

---

Impossible de mettre à jour; actuellement verrouillé(e).

---

Faut-il en déduire qu'il est impossible à un SGBD de gérer nativement (sans trigger) une mise à jour/suppression en cascade dans le cadre d'une relation réflexive ?


Remarques :

• mes recherches m'ont amené à découvrir que ce thème avait déjà été abordé, sans réponse définitive, dans la discussion suivante :
  http://www.developpez.net/forums/sho...d.php?t=166443

• une KB de Microsoft décrit le message d'erreur SQL Server que j'ai cité plus haut. A noter que la KB s'attarde plus sur la question des "accès en cascade multiples" que sur celle des "cycles", sujet de la présente discussion.

• mes recherches m'ont également conduit vers un topic soulevant la question du bien fondé de la mise a jour/suppression en cascade. Il est bien évident que la discussion créée aujourd'hui n'a pas pour objet de débattre du bien fondé de cette technique mais de savoir si, oui ou non, une mise à jour/suppression en cascade est techniquement possible dans le cadre d'une relation réflexive.

Bonjour,

Il a l'air de te refuser les deux cascades simultanés, ce qui ne veux pas dire qu'un cascade sur un delete uniquement ne marchera pas.
Il est par ailleurs très rare d'avoir un on update cascade, puisque généralement cela s'applique a des identifiant (du moins je l'espère pour la personne qui utilise la base de donnée). D'autant plus qu'il n'est pas rare de voir une double identification d'occurrence, une clé pour le système qui est soumise au contrôle d'intégrité, et une pour l'utilisateur parfois composé avec des lettres et autres réjouissance.
Ceci implique que souvent (et ils est préférable) les clés ne sont même pas connu de l'utilisateur. Ainsi puisqu'il n'est pas nécessaire de changer les valeurs des identifiants (le système se débrouille très bien avec ses valeurs numérique), il n'est plus utile répercuter ces modifications qui seront inexistante.
C'est pourquoi l'abscence du ON UPDATE dans Oracle par exemple n'est pas gênant, et si on viens a avoir besoin impérativement de changer les clé, il sera toujours possible de faire un trigger pour cela, ou un procédure ponctuelle.

Je ne suis pas du tout spécialiste de SQL Serveur, mais a mon sens qu'il te refuse un ON UPDATE et un ON DELETE a la fois, n'est pas une gène puisque je travaille généralement sans ON UPDATE.

Bonjour kazou et merci d'avoir pris le temps de me répondre.

Je ne l'avais pas précisé dans mon premier message, mais l'"erreur" que je cite se produit également lorsque j'essaie de mettre en place seulement une cascade de mise à jour ou seulement une cascade de suppression.

Ci-dessous les requêtes correspondantes :

Code:

---

1 2 3 4 5 6

CREATE TABLE test ( pk NUMERIC, fk NUMERIC, CONSTRAINT c1 PRIMARY KEY (pk), CONSTRAINT c2 FOREIGN KEY (fk) REFERENCES test (pk) ON UPDATE CASCADE);

---

Code:

---

1 2 3 4 5 6

CREATE TABLE test ( pk NUMERIC, fk NUMERIC, CONSTRAINT c1 PRIMARY KEY (pk), CONSTRAINT c2 FOREIGN KEY (fk) REFERENCES test (pk) ON DELETE CASCADE);

---

Dans ce cas c'est effectivement contraignant surtout que ton SQL a l'air ok.
Je pense qu'il va falloir attendre un expert en SQL Server :roll:

Bjr,

Je bosse sous postgreSQL et j'ai des tables avec relation réflexive, et UPDATE CASCADE.

non seulement, j'ai eu le droit de les créer mais cela fonctionne correctement.

+1 pour l'avis d'un expert SQL Server.

Bonjour J1,


Ah ! Ces SGBD staliniens....

Concernant les tables qui s’auto-référencent, avec DB2 for z/OS, depuis 1988 on peut coder :

Code:

---

1 2 3 4 5

CREATE TABLE test ( pk NUMERIC, fk NUMERIC, CONSTRAINT c1 PRIMARY KEY (pk), CONSTRAINT c2 FOREIGN KEY (fk) REFERENCES test (pk) ON DELETE CASCADE);

---

mais il vous est fait interdiction de coder ainsi la contrainte c2 :

Code:

---

CONSTRAINT c2 FOREIGN KEY (fk) REFERENCES test (pk) ON DELETE RESTRICT);

---

Et dans tous les cas de figure, que les tables s’auto-référencent ou non, DB2 interdit de coder ON UPDATE CASCADE. Je vous renvoie à ce que j’ai écrit dans Une petite couche supplémentaire (L'article étant un peu long, cherchez "Du temps où Ted Codd insistait").

Merci à vous, say et fsmrel.
Vos retours d'expérience m'indiquent que la mise en place d'une relation réflexive avec mise à jour/suppression en cascade est possible sur certains SGBD. Voilà qui me rassure un peu, mais quel dommage tout de même : SQL Server 2000 gérant les triggers récursifs, j'aurais cru qu'il gèrerait nativement ce type de relation.
Quoi qu'il en soit, je reste bien sûr à l'affût d'autres retours d'expérience, quel que soit le SGBD.

Quant à l'anecdote relative à la v2 de DB2, fsmrel, je dois dire qu'elle est en effet assez savoureuse !

Bonsoir J1,

J’observe que vous utilisez l’auto-référence : vous fournissez en effet l'exemple suivant :

Code:

---

1 2 3 4 5

pk fk ----- 1 1 2 1 3 2

---

dans lequel il existe une ligne ayant la valeur <1, 1>. Autrement dit, quel que soit x, vous autorisez la valeur <x, x>.
Est-ce la sémantique de votre application qui vous conduit à procéder ainsi ? Au-delà d’une certaine crainte (sinon crainte certaine) que m’inspire l’auto-référence (je n’ai pas oublié le paradoxe du barbier et le désarroi de Frege...), cela éveille en moi de la curiosité, car je n’ai jamais eu à procéder ainsi. J’ai mis en œuvre des nomenclatures de pièces, des hiérarchies : jamais d’auto-référence. Pour l’anecdote, j’étais intervenu chez les militaires et avais fait observer au Lt Colonel H* qu’il me paraissait suspect de trouver ce même genre de valeur <1, 1> dans ses tables. Je lui fis observer que SQLment parlant, il se trouvait hiérarchiquement au même niveau que le Colonel T* dont il était le bras droit : il me répondit "Reçu 5/5 !" et donna l’ordre au Lieutenant F* de rétablir les choses (notamment au niveau du MCD).
Concernant les bases de données, je pense qu’il y a un risque à pratiquer l’auto-référence, quand par exemple on utilise la jointure récursive. Je ne chercherai même pas à tenter de le faire, je crains les boucles infinies (et que le ciel me tombe sur la tête)...

En tout logique la mise en place d'autoréférence doit s'accompagner de mise en place de contrainte interdisant la présence d'une valeur <x,x>.

non?

Citation:

---

Envoyé par fsmrel

Je lui fis observer que SQLment parlant, il se trouvait hiérarchiquement au même niveau que le Colonel T* dont il était le bras droit : il me répondit "Reçu 5/5 !" et donna l’ordre au Lieutenant F* de rétablir les choses (notamment au niveau du MCD)

---

Je comprends pas cette explication (bon, on est le matin...hein, indulgence ;))
est-ce en rapport avec la présence possible de <x,x>?.
Quand vous dites au même niveau, c'est dans le sens dans la même table?

finalement la question n'est-elle pas : quel est l'intérêt de l'autoréférence?

Bonjour,

Citation:

---

Envoyé par fsmrel

Bonsoir J1,

J’observe que vous utilisez l’auto-référence : vous fournissez en effet l'exemple suivant :

Code:

---

1 2 3 4 5

pk fk ----- 1 1 2 1 3 2

---

dans lequel il existe une ligne ayant la valeur <1, 1>. Autrement dit, quel que soit x, vous autorisez la valeur <x, x>.
Est-ce la sémantique de votre application qui vous conduit à procéder ainsi ?

---

Etant donné la contrainte d'intégrité référentielle, j'ai deux solutions pour débuter une arborescence : soit des lignes qui s'autoréférencent, comme :

Code:

---

1 2 3 4

pk fk ----- 1 1

---

soit des lignes pour lesquelles la colonne fk n'est pas renseignée :

Code:

---

1 2 3 4

pk fk ----- 1 NULL

---

(dans ce cas, on définira évidemment la colonne fk comme étant nullable)

Le choix entre les 2 solutions est notamment une question de point de vue : estime-t-on que les lignes débutant une arborescence n'ont pas de parent (solution 2), ou qu'elles sont leur propre parent (solution 1)...

Citation:

---

Envoyé par fsmrel

Concernant les bases de données, je pense qu’il y a un risque à pratiquer l’auto-référence, quand par exemple on utilise la jointure récursive. Je ne chercherai même pas à tenter de le faire, je crains les boucles infinies (et que le ciel me tombe sur la tête)...

---

La crainte d'une boucle infinie (nous ne parlons donc plus ici de "simple" SQL, mais de SQL procédural, permettant de réaliser des boucles) est en effet justifiée. Mais, en admettant que l'on opte effectivement pour la solution des lignes autoréférencées, on peut alors ajouter dans la condition de jointure que pk doit être différent de fk.

Citation:

---

Envoyé par fsmrel

Est-ce la sémantique de votre application qui vous conduit à procéder ainsi ?

---

Concernant la question de la relation réflexive, je n'ai d'autre impératif que celui de satisfaire ma curiosité. Pas de contrainte particulière, juste l'envie d'en savoir plus sur un thème que je n'avais pas encore eu l'occasion d'approfondir.

Citation:

---

Envoyé par fsmrel

Pour l’anecdote, j’étais intervenu chez les militaires et avais fait observer au Lt Colonel H* qu’il me paraissait suspect de trouver ce même genre de valeur <1, 1> dans ses tables. Je lui fis observer que SQLment parlant, il se trouvait hiérarchiquement au même niveau que le Colonel T* dont il était le bras droit : il me répondit "Reçu 5/5 !" et donna l’ordre au Lieutenant F* de rétablir les choses (notamment au niveau du MCD).

---

Savoureux, à nouveau !

Citation:

---

Envoyé par say

Je comprends pas cette explication (bon, on est le matin...hein, indulgence ;))
est-ce en rapport avec la présence possible de <x,x>?.
Quand vous dites au même niveau, c'est dans le sens dans la même table?

---

Je pense que ce fsmrel veut dire, c'est que si l'on imagine la table Personnes suivante :

Code:

---

1 2 3 4 5 6

Nom NomDuSuperieur ----------- Col.T Col.T Col.H Col.T

---

le colonel T et le colonel H sont tous deux considérés comment subordonnés du colonel T.

Pour éviter cela, l'autre solution envisageable serait donc :

Code:

---

1 2 3 4 5 6

Nom NomDuSuperieur ----------- Col.T NULL Col.H Col.T

---

Si l'on transpose l'explication que je donnais tout à l'heure, cela reflète à nouveau 2 points de vue différents : le colonel T dirait-il qu'il n'a d'ordre à recevoir de personne (solution 2), ou dirait-il qu'il n'a d'autre maître que lui-même (solution 1) ?

fsmrel a tout a fait raison et a mis en évidence un détail que je n'avais pas remarqué.
A vrais dire dire qu'une occurrence fait référence a elle même est asses rare.
Lors qu'une jointure cela mènera a une boucle récursive infini, (c'est d'ailleurs une boucle si on représente sont graphe orienté).
Ceci couplé avec des [ ON UPDATE | ON DELETE ] CASCADE, le SGBD ne s'y retrouve plus car on lui dit, quand tu delete l'occurrence référence, tu delete l'occurrence elle même, et donc dans ce cas précis, il se trouve que la référence et la même occurrence ...

Citation:

---

Envoyé par J1

le colonel T et le colonel H sont tous deux considérés comment subordonnés du colonel T.

---

reçu 5/5 ;).

ceci dit...quant à choisir la solution 1 ou la solution 2... :?
dans le cas de la solution 1, ici depuis le début nous ne raisonnons que sur la racine de l'arbre hiérarchique...comment dès lors éviter qu'une classe hiérarchique inférieure s'autoréférence?
par ailleurs, cela met en effet en évidence la problématique soulignée par kazou.

j'utilise bcp de relation réflexive et il ne m'est jamais venu à l'idée qu'une entité se référence elle-même.

Citation:

---

Envoyé par fsmrel

rétablir les choses (notamment au niveau du MCD).

---

c'est-à-dire? mettre de côté les relations réflexives, en omettant certaines de leurs avantages?

Citation:

---

Envoyé par kazou

fsmrel a tout a fait raison et a mis en évidence un détail que je n'avais pas remarqué.
A vrais dire dire qu'une occurrence fait référence a elle même est asses rare.
Lors qu'une jointure cela mènera a une boucle récursive infini

---

Comme je l'écrivais dans mon précédent message, cela dépend de la condition de jointure.

Citation:

---

Envoyé par kazou

Ceci couplé avec des [ ON UPDATE | ON DELETE ] CASCADE, le SGBD ne s'y retrouve plus car on lui dit, quand tu delete l'occurrence référence, tu delete l'occurrence elle même, et donc dans ce cas précis, il se trouve que la référence et la même occurrence ...

---

Tout dépend de la manière dont le SGBD gère ses verrous lors de l'application de la cascade, mais en effet, la remarque est pertinente (particulièrement dans le cas d'un ON UPDATE CASCADE à mon avis). Merci kazou !

Tellement pertinente en fait que cela m'a donné envie d'aller consulter les options de verrouillage d'Access (Menu 'Outils' -> 'Options' -> 'Avancé'). Ce qui fait qu'au final, sous Access, je réussis à présent à mettre en oeuvre une jointure réflexive avec mise à jour/suppression en cascade !

Citation:

---

Envoyé par say

dans le cas de la solution 1, ici depuis le début nous ne raisonnons que sur la racine de l'arbre hiérarchique...comment dès lors éviter qu'une classe hiérarchique inférieure s'autoréférence?

---

C'est vrai, mais si nous venons sur ce terrain, la même question se pose aussi avec la solution 2 : comment "obliger" une classe hiérarchique inférieure à indiquer sa classe parente ?
Dans les deux cas, est-ce vraiment le rôle du SGBD ? En effet, on parle ici d'une donnée erronée (mais conforme aux règles de gestion) qui serait saisie : le SGBD en est-il responsable ?

PS : merci à tous pour cette discussion, que je trouve particulièrement intéressante !

Citation:

---

Envoyé par J1

Dans les deux cas, est-ce vraiment le rôle du SGBD ? En effet, on parle ici d'une donnée erronée (mais conforme aux règles de gestion) qui serait saisie : le SGBD en est-il responsable ?

---

Je dirais que le rôle principal d'un SGBD est le contrôle d'intégrité et donc éviter les non sens.
Je pense donc que c'est du ressort du SGBD de conserver la base de donnée cohérente. Ici c'est le cas puisqu'il n'est pas logique de lier une occurrence à elle même.
Cela se traduirait par éventuellement par un trigger qui a l'insert refuserait l'ajout en cas de référence vers la même occurrence, ou bien en corrigeant en posant un marqueur NULL.

Peut-être que le modèle merisier au travers de contrainte proposent une modélisation de ce type de référence, si quelqu'un trouve une modélisation de ce type de contrainte ça peut être intéressant.

Il me semble que say demande comment éviter qu'une classe hiérarchique inférieure s'autoréférence à partir du moment où on adopte la solution 1, c'est-à-dire lorsque l'on considère que tout en haut de l'aborescence, les classes doivent être autoréférencées.

Je lui répondais donc que ce n'était pas possible, tout comme il est impossible de forcer une classe hiérarchique inférieure à indiquer sa classe parente, à partir du moment où on adopte la solution 2.

Dans les 2 cas, ça me semble impossible, parce que c'est justement l'information saisie dans la table qui détermine si une classe est ou non hiérarchiquement inférieure.

Citation:

---

Envoyé par J1

C'est vrai, mais si nous venons sur ce terrain, la même question se pose aussi avec la solution 2 : comment "obliger" une classe hiérarchique inférieure à indiquer sa classe parente ?

---

tout à fait! me doutais que la remarque arriverait. Après qu'est ce qui est moins logique : une classe qui ne référence pas son hierarchique..on une classe qui se référence elle-même?
contraindre l'autoréférenement, ça doit se pouvoir se faire..tandis que contraindre le référencement de la classe parente...je vois pas.

Citation:

---

Envoyé par J1

Dans les deux cas, est-ce vraiment le rôle du SGBD ? En effet, on parle ici d'une donnée erronée (mais conforme aux règles de gestion) qui serait saisie : le SGBD en est-il responsable ?

---

d'accord avec kazou

Citation:

---

Envoyé par J1

Il me semble que say demande comment éviter qu'une classe hiérarchique inférieure s'autoréférence à partir du moment où on adopte la solution 1, c'est-à-dire lorsque l'on considère que tout en haut de l'aborescence, les classes doivent être autoréférencées.

Je lui répondais donc que ce n'était pas possible, tout comme il est impossible de forcer une classe hiérarchique inférieure à indiquer sa classe parente, à partir du moment où on adopte la solution 2.

Dans les 2 cas, ça me semble impossible, parce que c'est justement l'information saisie dans la table qui détermine si une classe est ou non hiérarchiquement inférieure.

---

je pense malgré tout qu'il doit être possible de poser des contraintes supplémentaires...mais peut-etre pas sur tous les SGBD. je bosse notamment sous pgsql, et je pense que c'est faisable. je jettes un oeil ;)

EDIT : je confirmes, ss Pgsql, j'ai

Code:

---

ONSTRAINT autoref CHECK (p_uid <> p_parent)

---

on doit pouvoir ajouter un appel à une PS pour vérifier des contraintes plus forte, de type hierarchie avec des données organisées de telle sorte à pouvoir appliquer tous les contrôles nécessaires.

J'en reviens à mon message précédent : si tu veux que le SGBD oblige l'autoréférencement des classes parentes, il faudrait qu'il sache que les classes sont parentes. Hors c'est justement le fait qu'elles soient ou non autoréférencées qui détermine si elles sont ou non parentes.

A moins qu'on ne se soit pas compris. :?

Remarque : par "parente", j'entends "en haut de l'arborescence"

Citation:

---

Envoyé par J1

J'en reviens à mon message précédent : si tu veux que le SGBD oblige l'autoréférencement des classes parentes, il faudrait qu'il sache que les classes sont parentes. Hors c'est justement le fait qu'elles soient ou non autoréférencées qui détermine si elles sont ou non parentes.

A moins qu'on ne se soit pas compris. :?

Remarque : par "parente", j'entends "en haut de l'arborescence"

---

on est bien d'accord.
Dans le cas avec lequel je bosse, j'ai intégré une donnée supplémentaire permettant d'identifier le niveau hierarchique qui permettrait alors d'affiner les contraintes. Ce que je n'ai pas fait..car l'ergonomie côté applicatif règle le problème...
je vois déjà venir la remarque consistant à dire que ce n'est donc plus le SGBD qui applique les règles de gestion :aie: mais je pense que ce serait gérable.
Encore une fois, cela ne sera probablement pas le cas avec d'autres systèmes...genre Access :?

Attendez-moi, vous allez trop vite !

Citation:

---

Envoyé par J1

nous ne parlons donc plus ici de "simple" SQL, mais de SQL procédural, permettant de réaliser des boucles

---

Considérez-vous cet exemple comme du genre procédural ou déclaratif ?

A ce sujet, je cite Bertrand Russel :

The method of "postulating" what we want has many advantages; they are the same as the advantages of theft over honest toil.

Ce qui revient dans notre contexte à dire que la récursivité est à la programmation impérative ce que le vol est au labeur honnête...

Citation:

---

Envoyé par J1

Mais, en admettant que l'on opte effectivement pour la solution des lignes autoréférencées, on peut alors ajouter dans la condition de jointure que pk doit être différent de fk.

---

Quand je dis que je crains l’auto-référence, c’est parce que je suis distrait et que je pourrais oublier la condition de jointure que vous évoquez. Et même si j’y pense, certains de mes collègues moins aguerris ou vigilants pourraient tomber dans le piège.

Je cite say : "En tout logique la mise en place d'autoréférence doit s'accompagner de mise en place de contrainte interdisant la présence d'une valeur <x,x>."

Il est évident qu’il faut la jouer "Ceinture, bretelles et épingle à nourrice" et prendre le maximum de précautions en enrichissant la création de la table Test d’une contrainte c3, valant quel que soit le niveau de la hiérarchie :

Code:

---

1 2 3 4 5 6

CREATE TABLE test ( pk NUMERIC, fk NUMERIC, CONSTRAINT c1 PRIMARY KEY (pk), CONSTRAINT c2 FOREIGN KEY (fk) REFERENCES test (pk) ON DELETE CASCADE), Constraint c3 check (pk <> fk);

---

Citation:

---

Envoyé par J1

juste l'envie d'en savoir plus sur un thème que je n'avais pas encore eu l'occasion d'approfondir

---

Nous ferez-vous part de vos découvertes, notamment en ce qui concerne le comportement des autres SGBD ? Il y a certainement matière...

Citation:

---

Envoyé par J1

Je pense que ce fsmrel veut dire, c'est que si l'on imagine la table Personnes suivante :

Code:

---

1 2 3 4

Nom NomDuSuperieur ----------- Col.T Col.T Col.H Col.T

---

le colonel T et le colonel H sont tous deux considérés comment subordonnés du colonel T.

Pour éviter cela, l'autre solution envisageable serait donc :

Code:

---

1 2 3 4

Nom NomDuSuperieur ----------- Col.T NULL Col.H Col.T

---

---

Certes ! Mais...

Citation:

---

Envoyé par J1

le colonel T dirait-il qu'il n'a d'ordre à recevoir de personne (solution 2), ou dirait-il qu'il n'a d'autre maître que lui-même (solution 1) ?

---

La solution 1 engendrerait des situations intéressantes pour notre colonel, si par exemple il donnait l’instruction suivante : "Tous mes collaborateurs et leurs collaborateurs jusqu’au dernier seront d'astreinte dimanche prochain" (sous-entendu : "pendant que moi j'irai aux champignons)".

Citation:

---

Envoyé par J1

soit des lignes pour lesquelles la colonne fk n'est pas renseignée :

Code:

---

1 2 3

pk fk ----- 1 NULL

---

(dans ce cas, on définira évidemment la colonne fk comme étant nullable)

---

Fatalement, si l’on veut éviter l’auto-référence, on déboule sur l’utilisation de NULL. Or, vous savez que je suis partisan de coder "NOT NULL" pour chaque colonne de chaque table. C’est pour moi un défi et un enjeu, à savoir rester dans une logique binaire et éviter les pièges qui nous guettent dans l’emploi d’une logique ternaire inhérente à l’emploi de NULL.

Aussi, pour répondre à l’interrogation de say :

Citation:

---

Envoyé par say

Citation:

---

Envoyé par fsmrel

rétablir les choses (notamment au niveau du MCD).

---

c'est-à-dire? mettre de côté les relations réflexives, en omettant certaines de leurs avantages?

---

Loin de moi une telle idée !

Je vois plutôt les choses ainsi :

Considérons les deux MCD suivants (AGL Power AMC)

http://www.fsmwarden.com/developpez/...ence)(MCD).jpg

MCD I et MCD II vont donner lieu respectivement à MLD I et MLD II

http://www.fsmwarden.com/developpez/...ence)(MLD).jpg


Observations :

Dans MCD I, la cardinalité 0,1 donne lieu dans MLD I à une colonne ChefId nullable. SI l’on préfère l’auto-référence, on remplace 0,1 par 1,1.

Dans MCD II, l’association-type Hierachiser est remplacée par une entité-type Hierarchie. La cardinalité 1,1 mise entre parenthèses exprime l’identification relative, ce qui se traduit dans MLD II par le fait que PsnId est clé primaire de la table Hierarchie.

Le DDL produit par AMC est le suivant (modifier le métabolisme de la table Hierarchie comme on l’entend (Cascade) et ajouter au besoin la contrainte HieC4).

Code:

---

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15

CREATE TABLE Personne ( PsnId Integer not null , Nom Varchar(48) not null , Fonction Char(48) not null , Constraint PsnC1 PRIMARY KEY (PsnId) );   CREATE TABLE Hierarchie ( PsnId Integer not null , ChefId Integer not null , Constraint HieC1 PRIMARY KEY (PsnId) , Constraint HieC2 Foreign key (PsnId) References Personne , Constraint HieC3 Foreign key (ChefId) References Personne , Constraint HieC4 check (PsnId <> ChefId) );

---

Maintenant, si le directeur de projets est intransigeant et exige que la suppression d’une personne entraîne celle de ses collaborateurs, de leurs collaborateurs, jusqu’au dernier, avec MCD II il peut y avoir un léger problème d’adéquation de la programmation et du fonctionnel. Je soumets cela à votre sagacité. Rien n'est jamais acquis...

Très intéressant.

Merci, encore une fois, fsmrel de nous bercer de philosophie et d'humour au milieu des discussions techniques.

Citation:

---

Envoyé par fsmrel

Fatalement, si l’on veut éviter l’auto-référence, on déboule sur l’utilisation de NULL. Or, vous savez que je suis partisan de coder "NOT NULL" pour chaque colonne de chaque table. C’est pour moi un défi et un enjeu, à savoir rester dans une logique binaire et éviter les pièges qui nous guettent dans l’emploi d’une logique ternaire inhérente à l’emploi de NULL.

---

A noter que NULL n'est pas une valeur mais un marqueur, c'est a dire qu'il représente l'absence de connaissance d'une valeur existante.
En effet quand on traite un NULL on traite bel et bien une valeur sans la connaitre.
C'est d'ailleurs aussi pour cela que la norme SQL impose de retourner chaque NULL, lors d'un distinct par exemple. En effet ces NULL représente une valeur que l'on ne connait pas, elle n'est donc pas évaluable et sans évaluation on se contente de retourner l'information directement. Suivant donc cette logique je ne suis pas contre les NULL, bien au contraire je pense que c'est idéal pour désigner une information non connue, chose qui arrive tous les jours.

Bien sûr cette théorie n'est pas suivi par tous les SGBD, et puis je peut me tromper ce n'est que ma manière de voir.

Ce que fsmrel veut dire c'est qu'on peut modéliser de manière à ne pas avoir de colonnes NULLables. Là ou le marqueur NULL désigne l'absence de valeur, la non présence d'un enregistrement la désigne aussi bien.

Ainsi en prenant l'exemple absurde suivant :

Entite1 = {Id_Entite}
ValeurAttributEntite1 = {#Id_Entite, Valeur}

Modélise bien un attribut de l'Entité1 nullable sans colonne nullable dans la table. Bon évidemment quand on fait une jointure externe, on retrouve le NULL en cas de non présence de la donnée :aie:.

Citation:

---

Envoyé par vmolines

Ce que fsmrel veut dire c'est qu'on peut modéliser de manière à ne pas avoir de colonnes NULLables. Là ou le marqueur NULL désigne l'absence de valeur, la non présence d'un enregistrement la désigne aussi bien.

---

Oui j'en conviens, ma réponse voulais simplement exprimer que les champs nullables n'était pas pour moi une chose de mal, même voir utile.

Encore beaucoup de choses intéressantes écrites dans les derniers messages, et trop peu de temps pour les étudier comme elles le mériteraient !
En quelques mots :

Citation:

---

Envoyé par fsmrel

Considérez-vous cet exemple comme du genre procédural ou déclaratif ?

---

N'y allons pas par quatre chemins : je ne connaissais pas la clause WITH. Je me garderai donc bien de la ranger d'emblée dans telle ou telle catégorie. Où la situerais-tu pour ta part ?
Au fait, est-elle largement implémentée dans le petit monde des SGBD ?
Remarque : une chose que j'ai omis de préciser lors de nos précédentes discussions : j'ai pris l'habitude de tutoyer sur le forum, mais si tu préfères que je te vouvoie, n'hésite pas à me le faire savoir, ça ne me dérangerait aucunement.

Citation:

---

Envoyé par kazou

C'est d'ailleurs aussi pour cela que la norme SQL impose de retourner chaque NULL, lors d'un distinct par exemple.

---

Le NULL a en effet un statut bien particulier en SQL. Malheureusement, l'exemple est mal choisi, car il se trouve que, dans le cadre d'un GROUP BY (donc d'un DISTINCT), il est en l'occurrence traité comme n'importe quelle valeur :

Citation:

---

If the grouping column contains a null value, that row becomes a group in the results. If the grouping column contains more than one null value, the null values are put into a single group. This behavior is defined in the SQL-2003 standard.

---

Source : http://msdn2.microsoft.com/en-us/library/ms187007.aspx

Pour s'en convaincre, on pourra exécuter les deux requêtes suivantes :

Code:

---

1 2 3 4 5 6 7 8 9 10 11 12 13

SELECT DISTINCT F1 FROM ( SELECT NULL AS F1 UNION ALL SELECT NULL) AS T;   SELECT F1 FROM ( SELECT NULL AS F1 UNION ALL SELECT NULL) AS T GROUP BY F1;

---

qui renvoient donc chacune une seule ligne :

Code:

---

1 2 3 4

F1 ---- NULL

---

Citation:

---

Envoyé par fsmrel

Nous ferez-vous part de vos découvertes, notamment en ce qui concerne le comportement des autres SGBD ? Il y a certainement matière...

---

Mes découvertes, c'est notamment sur ce topic que je les fais, en lisant les différents témoignages que chacun a pu faire au sujet de la relation réflexive. :P
Et comme vous tous, j'essaie bien sûr d'alimenter moi-même la discussion avec ce que j'ai expérimenté ces derniers jours, que ce soit sur Access ou sur SQL Server.

Je vous souhaite à tous un excellent week-end !

Tout à fait d'accord avec toi J1, et tu a raison de dire qu'un distinct sur des NULL donne un seul NULL dans la plupart des SGBD, mais ça ne veux pas dire que c'est forcément sémantiquement juste.
D'ailleurs dans ta citation, ils parlent de valeurs, hors un NULL n'est pas une valeur, et c'est même totalement le contraire puisqu'il désigne l'absence même de valeur.

Plus d'informations sur le NULL ici

Après tout ceci n'est qu'un point de vue et la théorie est très différente (ici en est la preuve).

Bonjour,

Citation:

---

Envoyé par J1

je ne connaissais pas la clause WITH. Je me garderai donc bien de la ranger d'emblée dans telle ou telle catégorie. Où la situerais-tu pour ta part ?

---

Hum... La structure de WITH est la suivante :

http://www.fsmwarden.com/developpez/...lin(BNF)30.jpg

Si donc on admet qu’une requête (Query) est déclarative, la clause WITH est du genre déclaratif.

N.B. En réponse à votre remarque, sur le forum, j'ai pris l'habitude du "vous".

Citation:

---

Envoyé par J1

Au fait, est-elle largement implémentée dans le petit monde des SGBD ?

---

Je dois vous avouer que je n'en sais rien. La clause WITH est définie dans le cadre de la norme SQL, à laquelle sont conformes SQL Server et DB2. Concernant Oracle, la jointure récursive est disponible, mais sous une forme différente, avec une performance au demeurant excellente (par exemple, 100 millisecondes pour extraire une nomenclature, dans une table de 500000 lignes, dans un secteur où l’on ne rigole pas avec les nomenclatures, à savoir celui de la grande distribution).

Styles utilisés pour effectuer une jointure récursive (requêtes non testées) :

Soit la table

I (Composant, Composé)

Pour obtenir les composants du composé "xyz" :

Selon la norme SQL (à faire valider par SQLpro) :

Code:

---

1 2 3 4 5 6

With V (Composant) As (Select Composant From I Where Composé = "xyz" UNION ALL Select I.Composant From V, I Where I.Composé = V.Composant) Select Composant From V ;

---

Avec Oracle :

Code:

---

1 2 3

Select Composant From I ... Start With Composé = "xyz" Connect By Prior Composant = Composé

---

Hors SQL, en relationnel pur (avec le langage Tutorial D) :

Code:

---

TCLOSE (I {Composé, Composant}) WHERE Composé = "xyz") {Composant}

---

Intéressant !
J'en ai profité pour parcourir le tutoriel de SQLPro, également très instructif.
Malheureusement, je ne pense pas que les SGBD que j'utilise actuellement implémentent WITH (surtout s'il est apparu avec la norme SQL:1999).
Mais ça reste bon à savoir, merci !

Citation:

---

Envoyé par J1

Remarque : une chose que j'ai omis de préciser lors de nos précédentes discussions : j'ai pris l'habitude de tutoyer sur le forum, mais si tu préfères que je te vouvoie, n'hésite pas à me le faire savoir, ça ne me dérangerait aucunement.

---

Citation:

---

Envoyé par fsmrel

N.B. En réponse à votre remarque, sur le forum, j'ai pris l'habitude du "vous".

---

J'interprète votre réponse comme une invitation au vouvoiement. Aucun problème ! ;)

Citation:

---

Envoyé par kazou

quand on traite un NULL on traite bel et bien une valeur sans la connaitre.

---

Pas forcément...
En préambule, NULL est pour moi le nom d’un petit personnage magique, unique mais multiforme (ou polymorphe, terme à la mode), très difficile à maîtriser, façon savonnette. Il n’est pas une valeur, il n’a pas de valeur, mais il se fait volontiers passer pour une valeur (exemple : Insert Into T Values (..., NULL, ...)). Ainsi, les auteurs de la norme SQL se sont fait piéger, en écrivant que la valeur de vérité UNKNOWN (Cf. le type BOOLEAN) et NULL sont la même chose. Si l’on n’y prend garde, NULL s'empresse de polluer les bases de données, les parasiter, les ravager à la manière des criquets. NULL est un malin, il va jusqu’à chercher à faire faire des erreurs aux optimiseurs (je reviendrai sur ce point).

Par exemple, quand JPhi33 explique à Chtouk qu’une modélisation approximative au niveau conceptuel peut avoir des conséquences funestes au niveau logique relationnel, il attire l’attention de notre jeune camarade sur l’irruption de NULL dans le résultat des requêtes et le met donc en garde. Ainsi, quand Chtouk met en scène des universitaires dont certains seulement sont des étudiants, JPhi33 lui explique la technique de la spécialisation, laquelle est particulièrement satisfaisante d’un point de vue sémantique, et conduit en plus au niveau logique à avoir deux tables tout à fait saines :
Une table UNIVERSITAIRE contenant les attributs communs à tous les universitaires, ainsi qu’une table ETUDIANT contenant les attributs ne caractérisant que les étudiants. Un de ces attributs spécifiques a pour nom EstBoursier et permet de savoir si un étudiant est boursier, oui ou non.
Avec ce système, au niveau tabulaire on peut coder "NOT NULL" pour chaque attribut, ce qui est un but à atteindre. Maintenant, que se passe-t-il si on décide de n’avoir qu’une seule table, à savoir la table UNIVERSITAIRE ? En toute logique, on autorisera NULL à se manifester.

Je reprends le contenu de la table UNIVERSITAIRE, quand celle-ci a phagocyté la table ETUDIANT :

Code:

---

1 2 3 4 5 6 7

Table UNIVERSITAIRE Ref NomUniv PrenomUniv EstBoursier --- ------- ---------- ----------- U-1 DUBOIS Aline NULL U-2 DUPONT Bertrand Non U-3 DURAND Charlène Oui U-4 MARTIN Didier NULL

---

DUBOIS et MARTIN ne sont pas des étudiants : en conséquence, contrairement à ce vous dites, kazou, par sa présence NULL ne signale pas ici la méconnaissance que nous aurions d’une valeur, mais bien le fait qu’être boursier ou non n’a aucun sens pour les universitaires qui ne sont pas des étudiants. Ce qui est fâcheux, c’est que si l’on admet que l’on puisse ignorer qu'un étudiant soit boursier ou non, on peut se retrouver dans la situation inconfortable suivante :

Code:

---

1 2 3 4 5 6 7 8

Table UNIVERSITAIRE Ref NomUniv PrenomUniv EstBoursier --- ------- ---------- ----------- U-1 DUBOIS Aline NULL U-2 DUPONT Bertrand Non U-3 DURAND Charlène Oui U-4 MARTIN Didier NULL U-5 MOREAU Exupère NULL

---

Et les choses commencent à se gâter, du fait de l’ambiguïté apportée par NULL qui, je le rappelle, est multiforme, et l’on ne sait plus quelle signification donner à NULL selon que l’universitaire s’appelle DUBOIS, MARTIN ou MOREAU : Il devient urgent de suivre le conseil de JPhi33, même si ce farceur de NULL cherchera évidemment à revenir par la fenêtre.

Pour lever le genre d’ambiguïté que je viens d’évoquer, Ted Codd avait mis en œuvre une logique quadrivaluée, selon laquelle les valeurs de vérité ne sont plus seulement TRUE, FALSE et UNKNOWN, mais TRUE, FALSE, APPLICABLE et INAPPLICABLE. Selon cette logique, lorsque vous dites "quand on traite un NULL on traite bel et bien une valeur sans la connaître", vous signifiez que NULL se déguise en APPLICABLE, ce qui dans l’exemple de Chtouk correspond à la situation "On se sait pas si MOREAU est boursier", tandis que dans le cas de DUBOIS, MARTIN, NULL se déguise clairement en INAPPLICABLE.

Pour la petite histoire, la logique quadrivaluée de Codd est "inapplicable" (sic), car, entre autres, des lois fondamentales telles que les lois de De Morgan sont mises en échec :

¬(p ET q)

et

¬p OU ¬q

ne sont plus des formules équivalentes : si p et q prennent respectivement les valeurs de vérité APPLICABLE et INAPPLICABLE, la 1re expression sera évaluée à INAPPLICABLE tandis que la 2e sera évaluée à APPLICABLE. De la même façon, l’équivalence suivante ne vaut plus :

∀x (p) ≡ ¬∃x (¬p)

Ou si vous préférez, "Tous les hommes sont mortels" n’est plus équivalent à "Il n’existe pas d’homme non mortel".



NULL et les optimiseurs :

Les optimiseurs sont généralement friands de ce que l’on appelle la fermeture transitive, ne serait-ce que pour rendre les requêtes plus performantes (c’est une de leurs missions...)

Considérez la séquence suivante :

Code:

---

1 2 3 4 5 6 7 8 9 10 11 12 13 14

Create table Dept ( DeptNo Char (4) not null , Primary key (deptNo)) ; Create table Emp ( EmpNo Char (4) not null , DeptNo Char (4) , Primary key (EmpNo) , Foreign Key (DeptNo) references Dept) ; Insert Into Dept Values ('d2') ; Insert Into Emp Values ('e1', 'd2') ; Insert Into Emp Values ('e2', Null) ;

---

Puis la requête (due à Chris Date) :

Code:

---

1 2 3 4

Select EmpNo From Dept d, Emp e Where Not (d.DeptNo = e.DeptNo And e.DeptNo = 'd1') ;

---

L’optimiseur devrait normalement appliquer la fermeture transitive en enrichissant ainsi la requête :

Code:

---

1 2 3 4 5

Select EmpNo From Dept d, Emp e Where Not (d.DeptNo = e.DeptNo And e.DeptNo = 'd1' And d.DeptNo = 'd1') ;

---

mais la présence possible du camarade NULL l’incitera à n’en rien faire. En exécutant les requêtes, vous noterez la différence dans les résultats.


Accessoirement, je rappelle ce qu’a écrit Codd concernant l’ensemble vide [The Relational Model for Database Management: Version 2 (Reading, Mass.: Addison-Wesley, 1990), page 188] :

"SQL fournit NULL comme résultat de certaines fonctions statistiques (telles qu’AVERAGE) appliquées à l’ensemble vide. Puisque le NULL de SQL a été introduit pour signifier le fait qu’une valeur (db-value) est inconnue, ça n’est pas un choix judicieux de signifier dans ce contexte quelque chose de complètement différent, à savoir qu’un résultat arithmétique est indéfini."

En l’occurrence, NULL se manifeste à nouveau, déguisé encore autrement (combien a-t-il de costumes ce Frégoli des bases de données ?)

Sur le WITH et le récursivité :
SQL Server, IBM DB2, Sybase et Firebird le gère, soit plus de 67% des parts de marché.
Oracle gère cela de manière spécifique (une grosse connerie entre nous) uniquement pour les arbres (les parcours de graphe ne sont pas gérés).
MySQL et PostGreSQL ne savent pas faire...

Bref avec Oracle qui gère cela à moitié cela représente plus de 87 % de parts de marché !

A +

Salve Fred !

Citation:

---

Envoyé par SQLpro

Sur le WITH et le récursivité : MySQL et PostGreSQL ne savent pas faire...

---

 
De fait, pour PostgreSQL il a fallu attendre la V12... :P

Citation:

---

Envoyé par Alain Defrance

A noter que NULL n'est pas une valeur mais un marqueur, c'est a dire qu'il représente l'absence de connaissance d'une valeur existante.
En effet quand on traite un NULL on traite bel et bien une valeur sans la connaitre.
C'est d'ailleurs aussi pour cela que la norme SQL impose de retourner chaque NULL, lors d'un distinct par exemple.

---

D'un DISTINCT comme d'un ALL (ALL étant par défaut)...

Citation:

---

Envoyé par Alain Defrance

En effet ces NULL représente une valeur que l'on ne connait pas, elle n'est donc pas évaluable et sans évaluation on se contente de retourner l'information directement. Suivant donc cette logique je ne suis pas contre les NULL, bien au contraire je pense que c'est idéal pour désigner une information non connue, chose qui arrive tous les jours.

Bien sûr cette théorie n'est pas suivi par tous les SGBD, et puis je peut me tromper ce n'est que ma manière de voir.

---

Si tous les SGBDR sont conforme à la norme en matière de NULL à quelques très rares exceptions près...
1) Oracle fait confusion du NULL avec la chaine vide. Un paramètre est à modifier pour contournement
2) Sybase SQL Server permet une unicité de NULL (et MS SQL server en a hérité). Contournement possible avec les index uniques filtrés (WHERE MaColonne IS NOT NULL)

Sinon il y a plein de chose drôles avec les NULL...Extrait de mon livre "SQL la synthèse" dont le chapitre 2 va paraître en ligne sur ce site bientôt.... (le chapitre 1 est ici...)

La présence du NULL provoque parfois des résultats qui peuvent surprendre au premier abord parce que parfois contre intuitive dans certains prédicats. Voici quelques exemples des problématiques dont il faut avoir conscience... Voici quelques exemples.

Exemple 2.45* – influence du NULL…

Code:

---

1 2 3 4 5 6 7 8 9

-- Avec la table suivante :   CREATE TABLE T_NUL (NUL_X INT, NUL_Y INT);   INSERT INTO T_NUL VALUES (1, 1); INSERT INTO T_NUL VALUES (1, 2); INSERT INTO T_NUL VALUES (NULL, 2); INSERT INTO T_NUL VALUES (1, NULL); INSERT INTO T_NUL VALUES (NULL, NULL);

---

Code:

---

1 2 3 4 5 6 7 8 9

-- dont le contenu est :   NUL_X NUL_Y ----------- ----------- 1 1 1 2 NULL 2 1 NULL NULL NULL

---

À la lecture, les paires de requêtes suivantes semblent donner le même résultat, or il n'en est rien...

Code:

---

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15

SELECT * FROM T_NUL WHERE NUL_X = NUL_Y OR NOT (NUL_X = NUL_Y); SELECT * FROM T_NUL;   NUL_X NUL_Y ----------- ----------- 1 1 1 2   NUL_X NUL_Y ----------- ----------- 1 1 1 2 NULL 2 1 NULL NULL NULL

---

Code:

---

1 2 3 4 5 6 7 8 9 10

SELECT SUM(NUL_X) + SUM(NUL_Y) AS N FROM T_NUL; SELECT SUM(NUL_X + NUL_Y) AS N FROM T_NUL;   N ----------- 8   N ----------- 5

---

Code:

---

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18

SELECT CASE WHEN NUL_X = NUL_Y THEN 1 ELSE 0 END AS TRUTH FROM T_NUL; SELECT CASE WHEN NOT(NUL_X=NUL_Y) THEN 1 ELSE 0 END AS TRUTH FROM T_NUL;   TRUTH ----------- 1 0 0 0 0   TRUTH ----------- 0 1 0 0 0

---

Il y a aussi le fait que NULL existe !
La requête suivante :

Code:

---

1 2 3 4 5

SELECT 1 AS N WHERE EXISTS(SELECT * FROM T_NULL WHERE NUL_X IS NULL AND NUL_Y IS NULL)

---

Renverra bien une ligne NULLe !