IdentifiantMot de passe
Loading...
Mot de passe oublié ?Je m'inscris ! (gratuit)
Navigation

Inscrivez-vous gratuitement
pour pouvoir participer, suivre les réponses en temps réel, voter pour les messages, poser vos propres questions et recevoir la newsletter

Langage SQL Discussion :

[Derby BDD] référencer un enregistrement dans une même table avec 2 clés étrangères


Sujet :

Langage SQL

  1. #1
    Membre à l'essai
    Profil pro
    Inscrit en
    Septembre 2007
    Messages
    21
    Détails du profil
    Informations personnelles :
    Localisation : France

    Informations forums :
    Inscription : Septembre 2007
    Messages : 21
    Points : 20
    Points
    20
    Par défaut [Derby BDD] référencer un enregistrement dans une même table avec 2 clés étrangères
    Bonjour,
    Mon problème est assez simple à expliquer, mais je n'ai pas de solution.
    Je développais une application java jdbc classique, interagissant avec une bdd mysql (l'application se connecte à un serveur mysql pour interroger et modifier la base).

    J'aimerais passer cette application en standalone, et il apparait que Derby est conçu (entre autres) pour ce cas de figure, c'est pourquoi j'ai décider de migrer ma base de données mysql vers une nouvelle base java (derby).

    Après avoir adapté à la syntaxe sql de derby le script sql généré par phpmyadmin pour le dump, je tombe sur le problème suivant :

    J'ai une table pièce, et une table ouverture.
    Une pièce peut avoir plusieurs ouvertures, et une ouverture peut avoir de 0 à 2 pièces. (une à deux pièces dans les faits, mais j'ai décidé de gérer cette contrainte au niveau logiciel).

    Pour cela, un enregistrement de la table ouverture référence un ou deux enregistrements dans la table pièce à l'aide de une ou deux clés étrangères.

    A la création de la base, cela nous fait donc spécifier deux contraintes quasi identiques.

    Voici le code correspondant à cette modélisation :

    Création des tables :

    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
     
     
    CREATE TABLE piece (
      idPiece int NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1),
      designation varchar(30) DEFAULT NULL,
      surface decimal(5,2) DEFAULT NULL,
      PRIMARY KEY (idPiece));
     
    CREATE TABLE ouverture (
    idOuverture int NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1),
    nomOuverture varchar(30) DEFAULT NULL,
    surfaceOuv decimal(5,2) DEFAULT NULL,
    direction varchar(10) DEFAULT NULL,
    idPiece1 int DEFAULT NULL,
    idPiece2 int DEFAULT NULL,
    PRIMARY KEY (idOuverture),
    CONSTRAINT orientation_chck CHECK (direction IN ('NORD','SUD','EST','OUEST','NORDEST','NORDOUEST','SUDEST','SUDOUEST'))) ;
    Contraintes liées :

    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    --
    -- Contraintes pour la table `ouverture`
    --
    ALTER TABLE ouverture ADD CONSTRAINT ouverture_ibfk_1 FOREIGN KEY (idPiece1) REFERENCES piece (idPiece) ON DELETE SET NULL ON UPDATE NO ACTION;
    ALTER TABLE ouverture ADD CONSTRAINT ouverture_ibfk_2 FOREIGN KEY (idPiece2) REFERENCES piece (idPiece) ON DELETE SET NULL ON UPDATE NO ACTION;
    Je compte créer la BDD à la mano pour l'embarquer dans mon jar, j'utilise donc l'utilitaire ij ou pour exécuter les scripts de création de table.
    A la création, la première contrainte de la table "ouverture" passe, mais pas la deuxième.
    L'utilitaire ij me retourne un message d'erreur :

    ERREUR 42915 : La clé externe 'OUVERTURE_IBFK_2' n'est pas valide car 'La table ne peut pas être définie comme étant dépendante de la table APP.PIECE en raison des limitations de la règle de suppression. Dans le cadre de la relation, une dépendance en cas de suppression s'établit entre cette table et la table indiquée en vertu de plusieurs relations et la règle de suppression de la relation existante est SET NULL. '.
    Le message est censé être clair, mais j'avoue être un peu perdu....
    Ce message semble indiquer que cette erreur est générée à cause de la clause ON DELETE SET NULL, mais la premiere contrainte (concernant la clé étrangère "idpièce1") comporte exactement la même clause sur exactement le même type de clé étrangère.

    Merci pour tout éclaircissement que vous jugeriez utile

  2. #2
    Modérateur
    Avatar de Waldar
    Homme Profil pro
    Customer Success Manager @Vertica
    Inscrit en
    Septembre 2008
    Messages
    8 452
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 46
    Localisation : France, Val de Marne (Île de France)

    Informations professionnelles :
    Activité : Customer Success Manager @Vertica
    Secteur : High Tech - Éditeur de logiciels

    Informations forums :
    Inscription : Septembre 2008
    Messages : 8 452
    Points : 17 814
    Points
    17 814
    Par défaut
    Avez-vous une erreur si vous ne rajoutez qu'une seule clef étrangère ?
    Ce n'est pas impossible que votre commande soit parcourue de bas en haut, d'où ma question.

  3. #3
    Membre à l'essai
    Profil pro
    Inscrit en
    Septembre 2007
    Messages
    21
    Détails du profil
    Informations personnelles :
    Localisation : France

    Informations forums :
    Inscription : Septembre 2007
    Messages : 21
    Points : 20
    Points
    20
    Par défaut
    Citation Envoyé par Waldar Voir le message
    Avez-vous une erreur si vous ne rajoutez qu'une seule clef étrangère ?
    Ce n'est pas impossible que votre commande soit parcourue de bas en haut, d'où ma question.
    Hello.
    Merci de la rapidité de votre réponse.
    Je n'ai pas d'erreur en n'exécutant le script qu'avec une seul contrainte à ajouter.
    Quand j'ajoute la seconde contrainte manuellement, la même erreur survient.
    C'est assez étrange.

  4. #4
    Membre à l'essai
    Profil pro
    Inscrit en
    Septembre 2007
    Messages
    21
    Détails du profil
    Informations personnelles :
    Localisation : France

    Informations forums :
    Inscription : Septembre 2007
    Messages : 21
    Points : 20
    Points
    20
    Par défaut
    J'ai fait quelques essais concernant les contraintes incriminées.
    Rappel des contraintes que j'aimerais définir pour ma bdd :

    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    ALTER TABLE ouverture ADD CONSTRAINT ouverture_ibfk_1 FOREIGN KEY (idPiece1) REFERENCES piece (idPiece) ON DELETE SET NULL ON UPDATE NO ACTION;
    ALTER TABLE ouverture ADD CONSTRAINT ouverture_ibfk_2 FOREIGN KEY (idPiece2) REFERENCES piece (idPiece) ON DELETE SET NULL ON UPDATE NO ACTION;
    Peu importe l'ordre dans lequel je les exécute, c'est toujours celle que j'exécute en deuxième qui coince.
    A noter que le moteur de base de donnée rejette avec un message toute contrainte de clé étrangère si elle concerne la même colonne dans la table "mère" (en l'occurence ici, la table pièce) et si les règles en cas de suppression sont différentes (ce qui est normal).

    Par contre, les contraintes passent quand je les mets en "ON DELETE CASCADE" ou en "ON DELETE NO ACTION"..... elles ne coincent que lorsque je les mets en "ON DELETE SET NULL"

  5. #5
    Membre à l'essai
    Profil pro
    Inscrit en
    Septembre 2007
    Messages
    21
    Détails du profil
    Informations personnelles :
    Localisation : France

    Informations forums :
    Inscription : Septembre 2007
    Messages : 21
    Points : 20
    Points
    20
    Par défaut
    Il s'agirait en fait d' une restriction implémentée dans Derby et non pas d'une hypothétique erreur de syntaxe ou de configuration.

    Avant que je ne change de SGBD embarqué, quelqu'un aurait une idée d'une manière de contourner le problème ?

    Créer une deuxième colonne dans la table piece qui serait la réplique de la première et sur laquelle je pourrais indexer la deuxième clé étrangère de la table ouverture, par exemple ?

    Merci d'avance

  6. #6
    Modérateur

    Avatar de CinePhil
    Homme Profil pro
    Ingénieur d'études en informatique
    Inscrit en
    Août 2006
    Messages
    16 793
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 60
    Localisation : France, Haute Garonne (Midi Pyrénées)

    Informations professionnelles :
    Activité : Ingénieur d'études en informatique
    Secteur : Enseignement

    Informations forums :
    Inscription : Août 2006
    Messages : 16 793
    Points : 34 024
    Points
    34 024
    Billets dans le blog
    14
    Par défaut
    Changer le modèle de données...

    Une pièce peut avoir plusieurs ouvertures, et une ouverture peut avoir de 0 à 2 pièces.
    MCD qui résulte de cette règle de gestion :
    piece -0,n----avoir----0,2- ouverture

    Comme je l'explique dans mon blog, à propos des cardinalités 0,1 - 0,n et en vertu de la bonne pratique consistant à ne pas autoriser de clé étrangère nulle, tout comme il faut bannir le bonhomme NULL des BDD relationnelles, aucune des deux tables ne peut recevoir de clé étrangère référençant l'autre puisque la cardinalité mini est à zéro des deux côtés.

    Il faut donc créer une table associative :
    piece (pce_id...)
    ouverture (ovr_id...)
    pce_avoir_ovr (pao_id_ouverture, pao_id_piece) + contrainte pour empêcher d'avoir 3 lignes avec la même pao_id_ouverture.
    Philippe Leménager. Ingénieur d'étude à l'École Nationale Supérieure de Formation de l'Enseignement Agricole. Autoentrepreneur.
    Mon ancien blog sur la conception des BDD, le langage SQL, le PHP... et mon nouveau blog sur les mêmes sujets.
    « Ce que l'on conçoit bien s'énonce clairement, et les mots pour le dire arrivent aisément ». (Nicolas Boileau)
    À la maison comme au bureau, j'utilise la suite Linux Mageïa !

  7. #7
    Membre à l'essai
    Profil pro
    Inscrit en
    Septembre 2007
    Messages
    21
    Détails du profil
    Informations personnelles :
    Localisation : France

    Informations forums :
    Inscription : Septembre 2007
    Messages : 21
    Points : 20
    Points
    20
    Par défaut
    Merci pour l'info, c'est très instructif, et cela explique en grande partie la raison pour laquelle il n'est pas permis de fixer deux contraintes sur une même colonne avec un "on delete set null" dans la bdd derby.

    Malheureusement, au point d'avancement où en est l'application et au vu des délais qu'il me reste, je serai contraint pour cette fois à l'hérésie qui consiste à passer les deux contraintes blocante en ON "DELETE NO ACTION" quitte à développer une moulinette qui purge les enregistrements dont les clés étrangères ne référencent aucun enregistrement dans la table mère.

    Ce sera toujours moins lourd que d'embarquer les binaires de Mysql dans mon livrable.

    Merci quand même car j'ai appris un truc

+ Répondre à la discussion
Cette discussion est résolue.

Discussions similaires

  1. [AC-2013] Copier enregistrement : dans une même table
    Par zkunk dans le forum VBA Access
    Réponses: 9
    Dernier message: 06/05/2015, 12h27
  2. [2008R2] Calcul de durée entre plusieurs enregistrements dans une même table
    Par mikmik60 dans le forum Développement
    Réponses: 2
    Dernier message: 20/10/2014, 14h28
  3. Réponses: 22
    Dernier message: 22/05/2013, 18h52
  4. Copier un enregistrement dans une même table
    Par slammer dans le forum Langage SQL
    Réponses: 11
    Dernier message: 05/05/2010, 00h17
  5. Réponses: 4
    Dernier message: 21/11/2007, 13h19

Partager

Partager
  • Envoyer la discussion sur Viadeo
  • Envoyer la discussion sur Twitter
  • Envoyer la discussion sur Google
  • Envoyer la discussion sur Facebook
  • Envoyer la discussion sur Digg
  • Envoyer la discussion sur Delicious
  • Envoyer la discussion sur MySpace
  • Envoyer la discussion sur Yahoo