Discussion :

[webshaker]

Salut.

J'ai 4 tables (membre, client, ami, utilisateur) qui héritent toutes de la table "contact"
J'ai créée une table "usr_group"

Code :

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

1
2
3
4
5
CREATE TABLE "usr_group" (
	"idgroup" serial,
	"group_name" text,
	PRIMARY key (idgroup)
);

Je veux à présent lier des contacts (quelque soit leur type) à une ou plusieurs entrée de la table usr_group
J'ai donc créé la table

Code :

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

1
2
3
4
5
6
CREATE TABLE "ctc_group_link" (
	"idgroup" integer not nul,
	"idcontact" integer not nul,
	PRIMARY key (idgroup, idcontact),
	FOREIGN key (idgroup) REFERENCES usr_group(idgroup)
);

Et c'est là tout le problème je n'ai pas trouvé comment placer une FOREIGN KEY permettant de s'assurer que idcontact est bien un contact !

Code :

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

	FOREIGN key (idcontact) REFERENCES contact(idcontact)

échoue car les un enregistrement dans la table "membre" n'est pas considéré comme un "contact".

La seule solution que j'aie trouvé est de développer une procédure stockée.
Mais c'est franchement pas terrible, car non seulement si demain je créée une nouvelle table fille de "contact" la procédure stockée ne sera alors plus à jour, mais en plus il faut plusieurs procédures stockées pour contrôler l'intégrité dans les deux sens !

Alors je me demande s'il n'y aurait pas moyen de définir une FOREIGN KEY quand même avec une option que je ne n'aurais pas vu !

Comment réglez vous ce problème vous ?

[punkoff]

Bonjour,

si toutes vos table héritent de contact, alors celles-ci ont comme clef primaire la clef primaire de contact.

Du coup je ne vois pas bien le problème.

[webshaker]

et bien non malheureusement,

Code :

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

1
2
3
 
ERROR:  insert or update on table "ctc_group_link" violates foreign key constraint "ctc_group_link_idcontact_fkey"
DETAIL:  Key (idcontact)=(1396060) is not present in table "contact".

Lors de l'Insert j'ai ça.

Code :

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

1
2
 
SELECT * FROM contact WHERE idcontact = 1396060;

retourne pourtant bien un enregistrement, mais l'enregistrement n'a pas été créé dans la table "contact" mais dans la table "membre".
Du coup le contrôle de la clé étrangère échoue.

[punkoff]

ok vous utilisez donc l'héritage de postgresql.

Il est bancale, cf la doc : http://www.postgresql.org/docs/9.3/s...l-inherit.html

Specifying that another table's column REFERENCES cities(name) would allow the other table to contain city names, but not capital names. There is no good workaround for this case.

These deficiencies will probably be fixed in some future release, but in the meantime considerable care is needed in deciding whether inheritance is useful for your application.

Pas de solution simple à votre cas.

Le mieux serait, de mon point de vue, de modéliser l'héritage d'un point de vue SQL.

Vous aurez qeulques trigger à mettre en place et des vues mais au moins vous n'aurez pas de problème de clef étrangère dans votre modèle ..

[webshaker]

Il va être trop tard pour abandonner l'héritage dans mon cas

Voilà ou j'en suis. (pour ceux qui auraient le même problème)
J'ai créé une procédure stockée qui permet de palier en partie au problème que j'ai

il faut déjà installer le module hstore qui va permettre d’accéder aux attributs de l'objet NEW.

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
34
35
CREATE OR REPLACE FUNCTION master_foreign_key() RETURNS TRIGGER AS $$
	DECLARE
		curs refcursor;
		id int;
		idchild integer;
   BEGIN
		IF TG_NARGS <> 3 THEN
			RAISE EXCEPTION 'master_foreign_key need to receive 3 parameters';
		END IF;
 
		IF TG_WHEN = 'AFTER' THEN
			idchild := (hstore(new)->TG_ARGV[0])::bigint;
 
			OPEN curs FOR EXECUTE 'SELECT * FROM ' || quote_ident(TG_ARGV[1]) || ' WHERE ' || quote_ident(TG_ARGV[2]) || ' = $1' USING idchild;
			FETCH curs INTO id;
			IF NOT FOUND THEN
				RAISE EXCEPTION '% on table "%" violates master_foreign_key
DETAIL:  Key (idobject)=(%) is not present in table "%"', TG_OP, TG_RELNAME, idchild, TG_ARGV[1];
			END IF;
			CLOSE curs;
			RETURN NEW;
		ELSE
			idchild := (hstore(old)->TG_ARGV[0])::bigint;
 
			OPEN curs FOR EXECUTE 'SELECT * FROM ' || quote_ident(TG_ARGV[1]) || ' WHERE ' || quote_ident(TG_ARGV[2]) || ' = $1' USING idchild;
			FETCH curs INTO id;
			IF FOUND THEN
				RAISE EXCEPTION '% on table "%" violates master_foreign_key
DETAIL:  Key (idobject)=(%) is still referenced from table "%"', TG_OP, TG_RELNAME, idchild, TG_ARGV[1];
			END IF;
			CLOSE curs;
			RETURN OLD;
		END IF;		
   END;
$$ LANGUAGE plpgsql;

puis on remplace le foreign key (qui ne marche pas) par:

Code :

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

CREATE constraint TRIGGER ctc_group_link_idcontact AFTER INSERT OR UPDATE ON ctc_group_link FOR EACH ROW EXECUTE PROCEDURE master_foreign_key('idcontact', 'contact', 'idcontact');

Les 3 paramètres sont:

• l'idcontact de la table ctc_group_link
• la table dans laquelle il faut faire la verification
• la clé dans la table dans laquelle il faut faire la verification

Du coup le trigger est suffisamment générique pour être ré-utilisé dans d'autres cas puisque toutes les tables et colonnes sont envoyées en paramètres.

Lors de la création d'un enregistrement dans ctc_group_link le trigger va vérifier qu'il existe bien un contact avec idcontact fournit.

L'idéal serait à présent que j'arrive a placer le même TRIGGER sur la table contact
Un truc genre

Code :

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

CREATE TRIGGER contact_panel_check BEFORE UPDATE OR DELETE ON contact FOR EACH ROW EXECUTE PROCEDURE master_foreign_key('idcontact', 'ctc_group_link ', 'idcontact');

Pour faire ça il faut 2 codes distinct que l'on appelle en fonction du moment (BEFORE ou AFTER) de l'invocation du TRIGGER

Mais cela ne fonctionne pas car malheureusement,

Code :

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

DELETE FROM contact;

n’exécute le TRIGGER que sur les entrées crées dans la table "contact".
Il faut donc appliquer le TRIGGER sur toutes les tables que héritent de "contact".

Code :

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

1
2
3
4
5
CREATE TRIGGER contact_panel_check BEFORE UPDATE OR DELETE ON contact FOR EACH ROW EXECUTE PROCEDURE master_foreign_key('idcontact', 'ctc_group_link ', 'idcontact');
CREATE TRIGGER contact_panel_check BEFORE UPDATE OR DELETE ON membre FOR EACH ROW EXECUTE PROCEDURE master_foreign_key('idcontact', 'ctc_group_link ', 'idcontact');
CREATE TRIGGER contact_panel_check BEFORE UPDATE OR DELETE ON client FOR EACH ROW EXECUTE PROCEDURE master_foreign_key('idcontact', 'ctc_group_link ', 'idcontact');
CREATE TRIGGER contact_panel_check BEFORE UPDATE OR DELETE ON ami FOR EACH ROW EXECUTE PROCEDURE master_foreign_key('idcontact', 'ctc_group_link ', 'idcontact');
CREATE TRIGGER contact_panel_check BEFORE UPDATE OR DELETE ON utilisateur FOR EACH ROW EXECUTE PROCEDURE master_foreign_key('idcontact', 'ctc_group_link ', 'idcontact');

Bon voilà.
C'est pas le top, mais ça fonctionne. C'est un peu meilleur que ce que j'avait avant puisque à présent le TRIGGER est générique.
Si quelqu'un a une meilleure solution, je prends !

Merci.