Discussion :

[Sve@r]

Bonjour à tous

Aujourd'hui dans mon appli j'ai eu besoin de lister toutes les tables (avec leur colonnes) liées par une contrainte d'intégrité à une table X.Y. C'était afin que mon appli autorise ou pas l'effacement de la ligne en cours sur la table X.Y (si cette ligne est nécessaire en tant que clef étrangère alors mon appli grise le bouton d'effacement).

J'ai réussi ma requête mais je l'ai fait de 2 façons différentes et j'aimerais avoir vos avis sur la meilleure des deux

Requête 1: je pars de pg_constraint et je descends sur pg_stat_user_tables et pg_attribute du coté "conrelid" et "confrelid"

Code :

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

1
2
3
4
5
6
7
8
9
select tbl2.schemaname, tbl2.relname, col2.attname
from pg_constraint
inner join (pg_stat_user_tables as tbl1 
	inner join pg_attribute as col1 on (col1.attrelid = tbl1.relid)
) on (tbl1.relid=pg_constraint.confrelid)
inner join (pg_stat_user_tables as tbl2
	inner join pg_attribute as col2 on (col2.attrelid = tbl2.relid)
) on (tbl2.relid=pg_constraint.conrelid)
where (tbl1.schemaname, tbl1.relname, col1.attname, pg_constraint.contype, col2.attnum)=('X', 'Y', 'colonne_concernée', 'f', pg_constraint.conkey[1]);

Requête 2: je pars de pg_stat_user_tables et je descends sur pg_attribute puis pg_constraint puis pg_attribute puis pg_stat_user_tables

Code :

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

1
2
3
4
5
6
7
8
9
10
select tbl2.schemaname, tbl2.relname, col2.attname
from pg_stat_user_tables as tbl1
inner join (pg_attribute as col1
	inner join (pg_constraint
		inner join (pg_attribute as col2
			inner join pg_stat_user_tables as tbl2 on (tbl2.relid=col2.attrelid)
		) on (col2.attrelid=pg_constraint.conrelid)
	) on (pg_constraint.confrelid=col1.attrelid)
) on (col1.attrelid=tbl1.relid)
where (tbl1.schemaname, tbl1.relname, col1.attname, pg_constraint.contype, col2.attnum)=('X', 'Y', 'colonne_concernée', 'f', pg_constraint.conkey[1]);

J'ai tenté un explain sur les deux requêtes mais le résultat est le même. J'aurais pourtant pensé qu'en partant de stat_user_tables (qui est la plus petite de toutes) les jointures générées seraient moins volumineuses donc que ce serait plus optimal mais visiblement je me suis fourvoyé...

Merci de vos retours...

[SQLpro]

Plus simple. passez par les vues normatives d'INFORMATION_SCHEMA !

A +

[Sve@r]

Plus simple. passez par les vues normatives d'INFORMATION_SCHEMA !

Merci mais ça ne convient pas (ou alors j'ai pas su trouver quelle vue il fallait prendre parmi les 150 qu'on trouve dans information_schema)
J'ai en effet tenté d'aller voir information_schema.constraint_table_usage et en effet je vois bien que ma table X.Y a des contraintes d'intégrité mais ça me donne juste le nom de la contrainte et je n'ai pas réussi à aller plus loin.
Or moi j'ai aussi besoin du champ lié par la contrainte (pour l'instant il s'agit de contraintes sur champs simples et non champs composés) afin de pouvoir ensuite faire une requête dans les tables voir s'il y a des lignes concernées par la contrainte.

En effet, imaginons une table "remise" définissant les remises possibles applicables dans un magasin (id, libelle, taux) et contenant les infos suivantes
1; Client régulier; 5%
2; Client important; 9%
3; Gros client et ami; 10%
3; Employé; 20%

Puis une table "client" (id; libelle; id_remise) contenant les clients suivants
1; TOTO; 1
2; TITI; 1
3; TUTU; 4

Bien entendu le magasin peut créer les remiseq qu'il souhaite ou supprimer celles qui sont obsolètes. Et en effet comme les remises 2 et 3 ne sont pas utilisées, mon appli autorise leur effacement (c'est un dirigeant quelconque du magasin qui l'utilise et je ne vais pas l'envoyer faire des reqûetes). Mais pas la remise "1; Client régulier" qui est utilisée par 2 clients. Ni la 4.

Donc pour ça, quand on est en gestion de la table "remise", mon appli récupère dans cette table la contrainte (liaison sur la table "client") ainsi que les champs liés par cette contrainte (remise.id et client.id_remise) puis je regarde si la remise de la ligne en cours est présent dans la table client. Si c'est le cas, je grise le bouton d'effacement.

Or je n'ai pas réussi, avec information_schema, à récupérer les champs en question.

Par ailleurs les identifiants de information_schema sont généralement "schema" + "name" qui sont des champs textuels. Donc des jointures sur ces vues demanderont des jointures sur des couples de strings, alors qu'avec les tables pg_ je passe par le relid qui est un champ simple de type int. Il se pourrait alors (simple hypothèse non vérifiée) que ce fait impacte sur la vitesse de requêtage...

[alassanediakite]

Salut
N'est ce pas mieux de laisser PostgreSQL gérer la contrainte (empêcher la suppression en cascade). L'utilisateur sera informer par un message d'erreur de l’échec de suppression.
@+

[Sve@r]

N'est ce pas mieux de laisser PostgreSQL gérer la contrainte (empêcher la suppression en cascade). L'utilisateur sera informer par un message d'erreur de l’échec de suppression.

Bonjour, et merci de ton hypothèse. Malheureusement ce n'est pas possible pour deux raisons

• tout d'abord je n'aime pas la procédure essai/échec et laisser l'utilisateur tenter d'effacer la ligne pour lui dire après coup "nan t'avais pas le droit". Je préfère empêcher en amont cette tentative, je trouve la démarche plus respectueuse et plus professionnelle. Sinon ça me rappelerait trop quand on attend 2h dans une administration quelconque et que arrivé au comptoir on te dise "il manque le formulaire 4A", vous reviendrez le mois prochain. Je ne veux pas que mes applis soient un miroir de cette dérive administrative.
• ensuite, je programme en Python avec la librairie psycopg2 pour interfcer Postgres. Et dans cette librairie, un échec bdd bloque toute transaction jusqu'à ce qu'un rollback soit demandé. Or je trouve dommage d'annuler X transactions réussies parce que la X+1 est en échec. Même si le delete semble être la seule transaction visible j'en ai d'autres cachées (je pose par exemple des verrous sur la ligne en cours pour empêcher la modif multiple, etc et un rollback me poserait alors des soucis à ce niveau)

Donc bien entendu dans mes contraintes d'intégrité référentielle j'y ai mis un restrict sur le delete donc quoi qu'il arrive le delete ne se fera pas. Mais je continue à vouloir vérifier avant si le delete est possible. Surtout que ma requête fonctionne (j'y ai rajouté cependant une contrainte supplémentaire sur pc_constraint.confdeltype='r' pour n'obtenir que les contraintes de restrictions sur le delete) et j'obtiens bien, pour les remises 1 et 4, un retour comme quoi elles sont bien utilisées. La seule chose c'est que je m'interrogeais sur la possibilité d'en trouver une encore plus rapide et il semble donc qu'il n'y en ait pas (j'ai tenté des explain sur les deux reqêtes avec de grosses bases et toujours le même résultat pour les deux).

[tse_jc]

Bonsoir,

Personnellement, je n'autorise jamais l'effacement, ne serait-ce que pour pouvoir conserver la consistance des calculs au niveau des historiques. Ca c'est pro.
Pour rester pro, admettons malgré tout que vous avez besoin de le faire. Deux cas de figure:
1) Le manager est persuadé qu'il peut effacer la promo car aucun produit ne la gère plus, et c'est le cas => Pas de soucis et rien à faire de particulier si ce n'est un ON DELETE CASCADE sur la contrainte pour éviter du code inutile.
2) Le manager est toujours persuadé qu'il peut effacer la promo, mais il s'est trompé (ça arrive aussi). Et là plutôt de désactiver le bouton (ici il ne comprendra pas car il est certain de son fait, et va considérer que c'est un bug), on affiche une boîte de dialogue avec les produits concernés avec la possibilité de les mettre à jour au niveau de la boîte de dialogue afin de valider l'opération initiale in fine.
- Dans le cas où cela ne plairait pas à votre client (ça peut arriver), vous pouvez remplacer automatiquement les produits concernés par un code promo qui veut dire "aucun code promo".

Par expérience, une interface pro= toujours avoir une réaction de l'interface sur une action de l'utilisateur, sinon y a un truc qui marche pas.
(On désactive cependant, toutes les fonctionnalités inaccessibles pour l'utilisateur, et sur un comportement "naturel"/choisi de l'interface).

Pour finir, vous pouvez également tester via effet de jointure sur une requête spécifique, si tout ce que je viens de vous dire ne vous convient pas.

En espérant avoir pu vous aider.

Jean-Christophe

[Sve@r]

Personnellement, je n'autorise jamais l'effacement, ne serait-ce que pour pouvoir conserver la consistance des calculs au niveau des historiques. Ca c'est pro.

J'ai aussi des tables historiques qui répertorient tous les changements importants (changement de quantité, de prix, de TVA). Et l'effacement des éléments importants (par exemple les clients) est un effacement "logiciel" (je fais juste passer un flag à false et en plus je le fais sur un trigger activé sur un delete donc je n'ai même pas à m'embêter avec ça dans l'appli) mais je ne le fais pas pour les éléments annexes (thèmes, collections, etc). Et en plus j'ai programmé mon trigger pour qu'il identifie qui dmeande le delete (si c'est demandé par l'admin ou par le propriétaire de la table alors le delete est appliqué réellement). Ca aussi c'est pro.

1) Le manager est persuadé qu'il peut effacer la promo car aucun produit ne la gère plus, et c'est le cas => Pas de soucis et rien à faire de particulier si ce n'est un ON DELETE CASCADE sur la contrainte pour éviter du code inutile.

Ben non, surtout pas de "on delete cascade" pace que je ne veux surtout pas que l'effacement d'une remise utilisée entraine l'effacement de tous les clients concernés !!! Donc un "on delete restrict" qui quoi qu'il arrive interdira l'effacement d'une remise utilisée et qui permettra quand-même d'effacer une remise non utlisée sans qu'il y ait du code inutile

2) Le manager est toujours persuadé qu'il peut effacer la promo, mais il s'est trompé (ça arrive aussi). Et là plutôt de désactiver le bouton (ici il ne comprendra pas car il est certain de son fait, et va considérer que c'est un bug), on affiche une boîte de dialogue avec les produits concernés avec la possibilité de les mettre à jour au niveau de la boîte de dialogue afin de valider l'opération initiale in fine.

C'est fait aussi. Toutes mes saisies d'infos n-1 (n client mais chaque client n'est associé qu'à une remise) possède la possibilité de modifier sa remise. Et toute saisie 1-n (une remise peut concerner plusieurs clients) affiche alors la liste des clients concernés avec possibilité d'en rajouter ou d'en supprimer. Sauf que ça apparait directement et non dans une boite de dialogue surexposée.

- Dans le cas où cela ne plairait pas à votre client (ça peut arriver), vous pouvez remplacer automatiquement les produits concernés par un code promo qui veut dire "aucun code promo".

Alors oui effectivement la promo était un mauvais exemple car certains clients n'ont aucune remise (le champ est alors à null). Mais j'ai d'autres items qui, eux, sont associés à une caractéristique précise (comme n produit et chaque produit ayant un thème obligatoire) dans lequel je ne veux pas qu'il n'y ait "aucune" info.

Pour finir, vous pouvez également tester via effet de jointure sur une requête spécifique, si tout ce que je viens de vous dire ne vous convient pas.

Alors oui effectivement il est possible de faire en dur un "select count(*) from client where id_remise=remise_en_instance_d_effacement" et adapter cette requête à tous les autres modules de saisie (select count(*) from produit where id_theme=theme_en_instance_d_effacement pour la saisie thème/produit et etc etc). Mais ce que j'ai voulu faire, c'est une requête "universelle". Partant (par exemple) de la table "thème", ma requête commence par identifier les tables et champs qui sont liés (theme.id_theme pour la table principale et produit.id_theme pour la table liée) puis je génère alors ensuite un "select count(*) from <table_liee>.<champ_lié> where <table_principale>.<id_principal> = <id_en_instance_d'effacement>." Justement parce que je ne voulais pas avoir à faire des requêtes adaptées à chaque cas individuel (en fait j'ai un objet "table" qui gère une table et dont tous les autres héritent et cette requête se trouve dans cet objet). Et, encore une fois, ça fonctionne même si mon choix de l'action à appliquer alors (griser le bouton) n'est pas le votre (afficher une seconde boite de dialogue) et mon sujet principal n'était donc pas de devoir créer cette requête mais justement parce que j'en ai créé deux (qui sont plus ou moins miroir l'une de l'autre), d'avoir des avis sur laquelle des deux était (dans l'esprit des uns et des autres) la plus plébiscitée...

En espérant avoir pu vous aider.

C'est toujours très apprécié

[tse_jc]

Il est possible que vous ayez quelques soucis de modèle.

Alors oui effectivement la promo était un mauvais exemple car certains clients n'ont aucune remise (le champ est alors à null).

Si la colonne peut être à NULL, cela signifie que la colonne est dépourvue de contrainte référentielle. J'espère que cela ne va pas vous compliquer les choses.

Ben non, surtout pas de "on delete cascade" parce que je ne veux surtout pas que l'effacement d'une remise utilisée entraine l'effacement de tous les clients concernés !!! Donc un "on delete restrict" qui quoi qu'il arrive interdira l'effacement d'une remise utilisée et qui permettra quand-même d'effacer une remise non utlisée sans qu'il y ait du code inutile

On est d'accord vu qu'il n'y a pas de DF entre un client et une remise.

++

[Sve@r]

Il est possible que vous ayez quelques soucis de modèle.

C'est vrai, nul n'est à l'abri d'une erreur...

Si la colonne peut être à NULL, cela signifie que la colonne est dépourvue de contrainte référentielle. J'espère que cela ne va pas vous compliquer les choses.

Ben non, Postgres autorise parfaitement une colonne "nullable" à être sous contrainte référentielle d'une autre. Simplement la contrainte référentielle ne s'applique que quand l'info n'est pas nulle.
On a d'ailleurs la même possibilité sur une colonne unique qui peut quand-même être "nullable". La contrainte d'unicité ne s'applique que sur les lignes ayant cette colonne pas nulle.
De même (si on veut élargir ce sujet) il est tout à fait possible de créer un index unique sur une condition en écrivant son index de cette façon: create unique index "xxx" on "table" (col1, col2, ..., coln) where "colX" valide condition. L'unicité ne s'appliquera que sur les couples (col1, col2, ..., coln) dont les lignes ont la "colX" valident la condition. Par exemple, pour mes clients où je transforme un effacement en un passage du flag actif à "false", j'ai créé des index d'unicités qui ne s'appliquent que sur les lignes ayant le flag actif à true...

[SQLpro]

Merci mais ça ne convient pas (ou alors j'ai pas su trouver quelle vue il fallait prendre parmi les 150 qu'on trouve dans information_schema)

Il faut utiliser :
INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS
et
INFORMATION_SCHEMA.KEY_COLUMN_USAGE
en sus de
INFORMATION_SCHEMA.TABLE_CONSTRAINTS

Un début de requête vous est donné dans mon cours :
http://sqlpro.developpez.com/cours/s...age=partie2#L9

Preuve que vous ne vous êtes même pas donné la peine de chercher !

J'ai en effet tenté d'aller voir information_schema.constraint_table_usage et en effet je vois bien que ma table X.Y a des contraintes d'intégrité mais ça me donne juste le nom de la contrainte et je n'ai pas réussi à aller plus loin.

Pas courageux ou feignant ?

...
Par ailleurs les identifiants de information_schema sont généralement "schema" + "name" qui sont des champs textuels. Donc des jointures sur ces vues demanderont des jointures sur des couples de strings, alors qu'avec les tables pg_ je passe par le relid qui est un champ simple de type int. Il se pourrait alors (simple hypothèse non vérifiée) que ce fait impacte sur la vitesse de requêtage...

Compte tenu du faible volume de ces tables c'est pas un problème la performance. Mais l'utilisation des tables système est à proscrire dans le cadre d'un développement. En effet, les vues d'information de schéma sont garanties quelques soit les version de PG et même quelque soit le SGBDR (sauf Oracle) puisque que c'est la norme SQL qui le définit et non l'éditeur. Or concernant les tables système, PG comme tous "fabriquant " de SGBDR peut changer du jour au lendemain et sans prévis la structure, le contenu et la forme des tables systèmes qui sont destiné à un usage interne. Et ces changements interviennent régulièrement !
Donc, votre requête risque de ne plus fonctionner ultérieurement !

A +

[Sve@r]

Un début de requête vous est donné dans mon cours :
http://sqlpro.developpez.com/cours/s...age=partie2#L9

Preuve que vous ne vous êtes même pas donné la peine de chercher !

Mille pardons, je ne savais pas que ce forum vous servait de support publicitaire. Punaise, même moi quand une info se trouve dans un de mes cours j'ai au-moins la décence d'écrire au minimum "aller voir mon cours à telle URL (et parfois même telle page)" !!!

Pas courageux ou feignant ?

Ni l'un ni l'autre dans la mesure où ma requête me convenant dans ses résultats et que, jusqu'ici, je n'avais pas eu de raison de ne pas utiliser les tables systèmes. Je considère donc plutôt comme preuve d'intelligence de ne pas réinventer la roue parce qu'un inconnu, fut-il référencé, me dit d'utiliser autre chose sans prendre la peine de justifier un minimum. Je pense qu'elle est là la vraie fainéantise.

Mais l'utilisation des tables système est à proscrire dans le cadre d'un développement. En effet, les vues d'information de schéma sont garanties quelques soit les version de PG et même quelque soit le SGBDR (sauf Oracle) puisque que c'est la norme SQL qui le définit et non l'éditeur. Or concernant les tables système, PG comme tous "fabriquant " de SGBDR peut changer du jour au lendemain et sans prévis la structure, le contenu et la forme des tables systèmes qui sont destiné à un usage interne. Et ces changements interviennent régulièrement !
Donc, votre requête risque de ne plus fonctionner ultérieurement !

Ca c'est une très bonne raison qui effectivement va m'inciter à retracer tous les endroits où j'utilise pg et remplacer par les vues d'information. C'est toutefois dommage car j'utilisais pas mal pg_stat_user_tables chaque fois que j'avais besoin de lister les tables de ma base et c'est dommage de remplacer un simple select schemaname, relname from pg_stat_user_tables par ce lourd select table_schema, table_name from information_schema.tables where table_type='BASE TABLE' and table_schema not in ('pg_catalog', 'information_schema'). Mais puisqu'il le faut...

[Sve@r]

Rebonjour

Suite aux remarques de SQLpro, j'ai tenté de remplacer les pg_ par information_schema. Malheureusement je pense que ce n'est pas possible.

J'avais réussi à écrire une requête qui me semblait parfaite

Code :

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

1
2
3
4
5
6
7
8
9
select distinct t.*
from information_schema.table_constraints as t
inner join (information_schema.referential_constraints as r
	inner join information_schema.key_column_usage as k
	on ((k.constraint_schema, k.constraint_name)
	=(r.unique_constraint_schema, r.unique_constraint_name))
) on ((r.constraint_schema, r.constraint_name)
=(t.constraint_schema, t.constraint_name))
where k.table_name='nom_table_dont_on_cherche_a_connaitre_par_qui_elle_est_referencée';

Sauf que quand j'ai testé cette requête sur les vraies tables primaires de ma base, ça m'a remonté des tables qui ne la référençaient pas. Si par exemple je demandais "refX" et que "tableX" référençais "refX" et que "tableY" référençais "refY", ben dans certains cas ça me donnait "tableX" et aussi "tableY".

J'ai mis du temps avant de comprendre le pb, qui vient du nom de la contrainte.

J'ai créé un petit jeu d'essai de démo

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
drop database if exists "bdd_test";
create database "bdd_test";
\connect "bdd_test"
 
create table "cat_couleur" (
	"id" smallint not null,
	"libelle" character varying(30) not null,
	primary key("id")
);
 
create table "cat_titre" (
	"id" smallint not null,
	"libelle" character varying(30) not null,
	primary key("id")
);
 
create table "voiture" (
	"id" smallint not null,
	"id_couleur" smallint,
	constraint "fk_integrity" foreign key ("id_couleur")
		references "cat_couleur" ("id") match full
		on update cascade on delete restrict,
	primary key("id")
);
 
create table "client" (
	"id" smallint not null,
	"id_client" smallint,
	constraint "fk_integrity" foreign key ("id_client")
		references "cat_titre" ("id") match full
		on update cascade on delete restrict,
	primary key("id")
);

Comme vous le remarquerez, les références se nomment toutes "fk_integrity". Après-tout, ce n'est pas interdit. Sauf que la table "referential_constraint" utilise le couple "schema+contrainte" comme clef !!!
Et donc quand je demande "cat_couleur" dans ma requête, j'obtiens "voiture" mais aussi "client" !!!

Voici le détail de ce qui se passe quand on part de "cat_couleur"

Code :

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

1
2
3
4
5
select * from information_schema.key_column_usage where table_name='cat_couleur';
 constraint_catalog | constraint_schema | constraint_name  | table_catalog | table_schema | table_name  | column_name | ordinal_position | position_in_unique_constraint 
-------------------+-------------------+------------------+-------------+--------------+-------------+--------------+----------------+-------------------------------
 bdd_test              | public                   | cat_couleur_pkey | bdd_test      | public           | cat_couleur | id                  |                    1 |                              
(1 row)

Ca me donne la contrainte "cat_couleur_pkey". Recherchons cette contrainte dans la table "referential_constraint"

Code :

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

1
2
3
4
5
select * from information_schema.referential_constraints where unique_constraint_name='cat_couleur_pkey';
 constraint_catalog | constraint_schema | constraint_name | unique_constraint_catalog | unique_constraint_schema | unique_constraint_name | match_option | update_rule | delete_rule 
-------------------+-------------------+-----------------+--------------------------+--------------------------+-------------------------+--------------+------------+-------------
 bdd_test             | public                   | fk_integrity        | bdd_test                        | public                             | cat_couleur_pkey           | FULL            | CASCADE       | RESTRICT
(1 row)

Ca me renvoie la contrainte "fk_integrity". Utilisons cette contrainte dans la table "table_constraint"...

Code :

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

1
2
3
4
5
6
select * from information_schema.table_constraints where constraint_name='fk_integrity';
 constraint_catalog | constraint_schema | constraint_name | table_catalog | table_schema | table_name | constraint_type | is_deferrable | initially_deferred 
-------------------+-------------------+-----------------+--------------+--------------+------------+----------------+-------------+--------------------
 bdd_test              | public                  | fk_integrity        | bdd_test        | public           | voiture       | FOREIGN KEY     | NO              | NO
 bdd_test              | public                  | fk_integrity        | bdd_test        | public           | client         | FOREIGN KEY     | NO              | NO
(2 rows)

Et voilà, on a au final la table "voiture" mais aussi la table "client" alors que cette table ne fait absolument pas référence à la table "cat_couleur".
Donc déjà, quand on est un pro on évite de balancer des solutions non testées en disant que ça marche. Et surtout on évite les insultes gratuites !!!

Et donc même si pg est susceptible d'évoluer, on ne peut pas s'en passer.

[SQLpro]

Comme vous le remarquerez, les références se nomment toutes "fk_integrity". Après-tout, ce n'est pas interdit.

Vous commettez une grossière erreur. Le nom de tout objet dans une base SQL que ce soit une table une vue une fonction ou une contrainte c'est nom_schéma + nom_object, les contraintes ne faisant pas exception à la règle. Revoyez votre jointure et vous obtiendrez la bonne réponse !

[Sve@r]

3 ans après, le problème est enfin résolu
Vieux motard etc ...