Le portail francophone de la géomatique

Le couple clé primaire/étrangère permet de garantir l'intégrité référentielle
de deux tables, c'est à dire qu'une ligne de la table A référencée dans
une table B ne peut être supprimée à moins que les lignes de la table B
la référençant supprime cette référence en modifiant la valeur de leur clé étrangère.
Pour se faire on crée deux colonnes respectivement dans les tables A et B, appelons les
respectivement id_a et fk_b_a, et pour qu'une ligne de B référence une ligne de A,
il faut que ces deux colonnes possèdent la même valeur.

C'est le travail du développeur de gérer ce genre de choses, car le moteur de base de données
n'a aucune idée a priori quelle ligne est censée être référencée dans quelle autre, c'est la logique
de votre code qui le permettra.
Il faut donc trouver un moyen de récupérer les clés primaires des lignes que vous insérez.

Une possibilité est d'utiliser le mot clef RETURNING a la fin de vos instructions insert afin que les requêtes
retourne les valeurs des clés primaires, et les utilisez dans vos commandes ultérieures.

Une autre serait de ne pas utiliser le mécanisme de valeur par défaut
lors d'une insertion mais de récupérer directement des valeurs des différentes séquences
en faisant un appel à la fonction nextval(id_table_sequence), et l'utiliser par la suite dans votre code.

Vous pourriez aussi gérer l'unicité des identifiants de vos tables uniquement par le code
(exemple : algorithme HI/LO https://vladmihalcea.com/the-hilo-algorithm/),
ou utiliser un identifiant naturel et donc vous passez de générer des identifiants artificiels.

Bonjour,

Utiliser RETURNING est le plus simple il me semble pour une petite application: on laisse le type serial se débrouiller.

J'ai souvent vu, dans des articles, les CTE utilisées en update/insert, notamment pour réaliser un "upsert" qui est désormais dispo dans PG.

Jamais testé dans des appli, mais un truc comme ca:

with insert_panneau as (
  insert into panneau (etat) 
  values ('cassé')
  returning id
), insert_tech as (
  insert into tech(nom) 
  values ('bob') 
  returning id
) insert into panneau_tech (id_panneau, id_tech)
    select p.id, t.id
  from insert_panneau p, insert_tech t;

Permet de faire les trois inserts dans une meme requete, donc une meme transaction, ce qui évite d'avoir à gérer les transactions dans le code (la plupart des drivers PG sont en autocommit).

Si votre appli est publique ou utilisée par beaucoup de personnes, attention dans votre code PHP à protéger les valeurs saisies dans les formulaires, en utilisant peut etre des prepared statements ou les outils PHP pour s'assurer que les valeurs reçues ne sont pas de l'injection SQL.

Nicolas

On peut aussi utiliser une couche d'abstraction pour l'accès aux bases de données,
ou DBAL, déja écrite comme par exemple les ORM (Object Relational Mapper).

Certaines d'entre elle sont difficiles à appréhender,
mais souvent ça vaut le coup de s'y essayer, car une fois les API
et leur mécanismes appris, on peut écrire des applications
avec beaucoup moins de code rébarbatif.
Ils encapsulent souvent aussi les problèmes complexes,
comme l'injection SQL ou la gestion des identifiants et
clé etrangères.

Malgré tout, il est bon de se confronter a cette complexité
bas-niveau, de manière à comprendre ce qui se passe
sous le capot, et cerner les limites de cette couche

Après tout dépends comme le dit Nicolas, de la taille
et de la destination de votre application.

Une petite recherche sur les DBAL Php donne par exemple :
https://www.doctrine-project.org/

Dernière modification par tumasgiu (Tue 12 June 2018 21:58)

Bjr Nicolas!
J'ai adapté le bout de code que vous m'avez proposé dernièrement, il insère super bien les données dans la table panneau_technicien.
Merci bien!