Vous n'êtes pas identifié(e).
Bonjour,
Quelle est la façon la plus élégante pour restreindre le champ d'une foreign key (c'est à dire faire une foreign key sur une vue, si c'était possible) ?
Je vois deux solutions :
- soit faire la foreign key sur la table, puis gérer les restrictions via un trigger on insert/update
- soit passer par des vues matérialisées
Concernant la 2ème options, les vues matérialisées ne sont pas gérées dans PostgreSQL. J'ai trouvé sur le net la méthode de Jonathan Gardner pour gérer les vues matérialisées, mais ça date de 2004. C'est toujours encore ce qu'il y a de mieux ? (http://www.benjaminarai.com/benjamin_ar … _views.php)
Plus globalement, quelle technique employez-vous quand vous souhaité sécuriser un peu mieux l'intégrité de vos données en restreignant le champ d'application d'une clé étrangère ?
Hors ligne
Sur quoi voulez vous restreindre «le champ d'une foreign key» ?
Pour ce qui est des vues matérialisées, c'est toujours aussi manuel sous Postgres pour le moment.
Marc.
Hors ligne
supposons le cas suivant (pour être plus clair sur ce que je cherche à faire, je fais "comme si" on pouvait faire une foreign key sur une vue)
create table toto (
id integer primary_key
nom varchar,
valide boolean default false;
);
create view vtoto as select * from toto where valide = true;
create table titi (
name varchar primary key,
toto_id integer references vtoto(id);
);
la syntaxe est bien sur incorrecte vu qu'on ne peut pas faire de foreign key sur une vue, ma question est simplement : comment gérez-vous ce genre de situation (à savoir que dans le champ toto_id de la table titi je ne veux autoriser que des "id" de la table toto pour lesquels valide est TRUE)
à priori le plus simple serait le trigger, mais je me demandais si les vues matérialisées étaient indiquées dans ce cas, ou s'il y a une autre solution à laquelle je ne pense pas ?
)
Hors ligne
Pas la peine de faire un trigger pour si peu: vous pouvez le faire simplement avec une contrainte check qui appelle une fonction. Évidemment, vous n'aurez pas les options 'CASCADE' par exemple.
Quelque chose comme CHECK ( verif_valide(toto_id))
Et définir la fonction verif_valide comme suit (non testé, mais c'est pour vous donner une idée de la chose):
CREATE FUNCTION verif_valide(int) RETURNS BOOLEAN LANGUAGE SQL AS $$
SELECT EXISTS (SELECT 1 FROM toto WHERE VALIDE=true AND id=$1;
$$;
Marc.
Hors ligne
ah bah oui en effet, tout simplement !
comme quoi j'ai bien fait de demander :-)
merci !
Hors ligne
Tiens, j'ai oublié la parenthèse fermante de mon EXISTS 
Marc.
Hors ligne
je vous pardonne...
Hors ligne
Pas la peine de faire un trigger pour si peu: vous pouvez le faire simplement avec une contrainte check qui appelle une fonction. Évidemment, vous n'aurez pas les options 'CASCADE' par exemple.
Quelque chose comme CHECK ( verif_valide(toto_id))
Et définir la fonction verif_valide comme suit (non testé, mais c'est pour vous donner une idée de la chose):
CREATE FUNCTION verif_valide(int) RETURNS BOOLEAN LANGUAGE SQL AS $$
SELECT EXISTS (SELECT 1 FROM toto WHERE VALIDE=true AND id=$1;
$$;
Non, ta solution est fausse. Car que fais t-on si valide passe de true à false ?
En fait il n'existe aucun solution simple car il s'agit d'une contrainte dynamique. Ce serait possible si PG implémentait les assertions. Dans ce cas, l'ordre SQL serait :
CREATE ASSERTION A_TOTO_TOTI
AS
CHECK NOT EXIST(SELECT *
FROM titi
WHERE toto_id IN (SELECT id
FROM toto
WHERE valide IS TRUE));Malheureusement ce n'est pas possible et rare sont les SGBDR à la faire car cela pose des problèmes de performance...
La seule solution reste donc aux trggers et il en faut deux :
1 sur INSERT + UPDATE sur la table TITI
1 sur INSERT + UPDATE sur la table TOTO
A +
Dernière modification par SQLpro (29/05/2011 10:09:47)
Frédéric Brouard, alias SQLpro, ARCHITECTE DE DONNÉES, Expert langage SQL
Le site sur les SGBD relationnel et langage SQL : http://sqlpro.developpez.com/
Modélisation de données, conseil, expertise, audit, optimisation, tuning, formation
* * * * * Enseignant CNAM PACA, ISEN Toulon, CESI Aix en Provence * * * * *
Hors ligne
En effet, la solution de Marc Cousin fonctionne bien lors de l'insertion et update dans titi, par contre elle ne gère pas le cas ou la valeur de valide dans toto change. Si valide passe a false, titi possèdera un champ toto_id referençant un enregistrement qui sera passé à false.
je pense que c'est ce que Marc voulait dire dans son message quand il disait "vous n'aurez pas les options CASCADE'" : l'intégrité référentielle n'est plus garantie
pour le garantir, il faut en effet un trigger sur TOTO
Mais à mon avis, on n'a pas besoin de trigger sur TITI, la contrainte CHECK suffit
c'est d'ailleurs la solution que j'ai retenue :
- contrainte CHECK dans TITI
- trigger sur TOTO pour empêcher de mettre valide à FALSE quand il y a des enregistrements dans TITI
Hors ligne
C'est exactement ce que j'entendais par 'ça ne cascade pas': toute modification sur l'autre table n'est pas vérifiée par la contrainte check simple que j'avais donnée. J'aurais du détailler davantage.
On peut évidemment résoudre le problème avec un trigger sur toto. Ou une contrainte check… ce que je trouve plus lisible (j'ai tendance à chercher les contraintes d'intégrité dans les contraintes avant d'aller farfouiller dans les triggers), comme de mettre un nom de fonction bien explicite…
Le but de mon exemple était bien de simplement vous montrer qu'on pouvait mettre des fonctions dans le check, ce qui permettait de résoudre ce genre de problématique.
Marc.
Hors ligne
on est d'accord
la solution finale que j'ai retenue est composée
- d'une foreign key
- d'une contrainte check
- et d'un trigger ON UPDATE sur la table toto, pour simuler le RESTRICT (on aurait bien sur aussi pu écrire le trigger différemment si on voulait simuler le CASCADE)
je pense en effet que c'est plus efficace que de mettre deux triggers.
ma solution finale
create table toto (
id integer primary_key
nom varchar,
valide boolean default false;
);
create table titi (
name varchar primary key,
toto_id integer references toto(id)
constraint toto_id_check check (verif_valide(toto_id))
);
et le trigger (sur la table toto) :
CREATE OR REPLACE FUNCTION toto_verif_id_utilise_ou_pas() RETURNS TRIGGER AS $$
DECLARE
compteur INTEGER := 0;
BEGIN
IF ((OLD.valide = TRUE) AND (NEW.valide = FALSE)) THEN
SELECT COUNT(*) INTO compteur
FROM titi
WHERE toto_id = NEW.id;
END IF;
IF (compteur > 0) THEN
RAISE EXCEPTION 'Vous ne pouvez pas mettre valide à FALSE car cet id est utilisé dans la table titi.';
RETURN NULL;
END IF;
END;
$$ LANGUAGE PLPGSQL;
CREATE TRIGGER toto_verif_id_utilise_ou_pas BEFORE UPDATE ON toto
FOR EACH ROW EXECUTE PROCEDURE toto_verif_id_utilise_ou_pas();
Hors ligne
euh...après réflexion, il y a encore plus simple, c'est d'écrire une deuxième fonction check (dans la table toto cette fois, appelons la count_totoid_dans_titi()). Cette fonction renvoie, pour un toto_id donné, le nombre de lignes présentes dans la table titi. Le check vérifie que si valide est mis à false, alors count_totoid_dans_titi doit renvoyer impérativement 0. Je suppose qu'il faudra mettre un CASE dans le check.
donc au finish ça ferait 2 contraintes CHECK, au lieu de 2 triggers
ça me semble en effet plus élégant
je vais m'y coller...
Hors ligne
yep, ça semble marcher avec deux CHECK
donc ci-dessous ma solution finale (on espère)
en terme de code à écrire, les deux solutions me semblent équivalentes...mais ça semble en effet plus élégant avec deux CHECK qu'avec deux TRIGGERS
create table toto (
id integer primary_key
nom varchar,
valide boolean default false;
);
create table titi (
name varchar primary key,
toto_id integer references toto(id)
constraint toto_id_check check (verif_valide(toto_id))
);
CREATE FUNCTION count_toto_id_dans_titi (IN id INTEGER) RETURNS BOOLEAN AS $$
SELECT EXISTS (SELECT name
FROM titi
WHERE toto_id = $1);
$$ LANGUAGE SQL;
ALTER TABLE toto
ADD CONSTRAINT check_toto_id_utilise_dans_titi
CHECK ( CASE WHEN valide = FALSE
THEN count_toto_id_dans_titi(id) = FALSE
END);
Hors ligne
question quand même à Frédéric Brouard (dont le livre SQL 2ème édition m'a fait passer quelques nuits blanches )
pourquoi opteriez-vous pour 2 triggers plutôt que 2 checks ? des différences en terme de performances ?
Hors ligne
Il y a quand même un cas qui ne va pas marcher avec cette solution:
état initial: titi n'a pas d'enregistrements référençant toto.
T1: update toto et passe valide à false
T2: insère des enregistrements dans titi
T1: commit
T2: commit
On doit pouvoir s'en sortir en mettant un select for share sur l'enreg de toto dans verif_valide.
Marc.
Hors ligne
exact, j'ai fait le test avec deux transactions concurrentes, le problème se pose en effet
et encore exact, en mettant FOR SHARE dans la fonction verif_valide ca regle le probleme, j'ai testé aussi
super!
Hors ligne
tient, je viens de devenir membre silver, comment ça se fait ?
ça a droit à un porte-clé ou un pins, un membre silver ?
Hors ligne
Non, c'est juste par rapport au nombre de messages dans le forum
Guillaume.
Hors ligne
Ouais, on est trop sympas ici, on donne les badges super vite…
Marc.
Hors ligne
pas de badge ? flute alors...
sans vouloir être pinailleur, pour clôturer ce post j'aimerais quand même savoir : entre la solution avec 2 CHECKS et la solutions avec 2 TRIGGERS préconisée par Frédéric Brouard, y aurait-t-il des raisons objectives de préférer une solution par rapport à l'autre ? (à part l'aspect "c'est plus élégant", qui est vous en conviendrez tout à fait relatif)
Dernière modification par bebert73 (30/05/2011 09:16:15)
Hors ligne
je me réponds partiellement à moi-même : dans certains cas les triggers sont quand même préférables
voir l'exemple de mon autre post, relatif à la désactivation des contraintes check : dans le cas où on n'a pas deux tables, mais une seule table avec une contrainte check "circulaire" (ie d'une colonne sur une autre de la même table), un problème se pose lors de la restauration, car à priori on ne peut pas désactiver les contraintes check
on est donc obligé dans ce cas d'utiliser des triggers, qu'on peut désactiver lors d'une sauvegarde / restauration
Hors ligne
je me réponds partiellement à moi-même : dans certains cas les triggers sont quand même préférables
voir l'exemple de mon autre post, relatif à la désactivation des contraintes check : dans le cas où on n'a pas deux tables, mais une seule table avec une contrainte check "circulaire" (ie d'une colonne sur une autre de la même table), un problème se pose lors de la restauration, car à priori on ne peut pas désactiver les contraintes check
on est donc obligé dans ce cas d'utiliser des triggers, qu'on peut désactiver lors d'une sauvegarde / restauration
J'ai fait des tests sur SQL Server (pas sur PG) pour un livre à paraître aux US, sur la comparaison des contraintes complexes entre trigger et check avec fonction. La fonctions gagne dans 95% des cas par rapport aux triggers.
Comme le moteur PG est, à la base, le même (ingres) je pense que les résultats seraient similaires avec PG.
Mais dans le doute, seul un benchmark serait le bienvenu !
A +
Dernière modification par SQLpro (31/05/2011 14:08:16)
Frédéric Brouard, alias SQLpro, ARCHITECTE DE DONNÉES, Expert langage SQL
Le site sur les SGBD relationnel et langage SQL : http://sqlpro.developpez.com/
Modélisation de données, conseil, expertise, audit, optimisation, tuning, formation
* * * * * Enseignant CNAM PACA, ISEN Toulon, CESI Aix en Provence * * * * *
Hors ligne
Ah non, le moteur PG n'est pas ingres. C'est juste le même créateur, au départ.
Quelqu'un a déjà fait ce test sur PostgreSQL, sinon?
Hors ligne
Ah non, le moteur PG n'est pas ingres. C'est juste le même créateur, au départ.
Quelqu'un a déjà fait ce test sur PostgreSQL, sinon?
Oui, enfin, ce sont les mêmes prinipes, algo et tutti quanti qui sont mis en jeu...
Lisez ce que raconte Stonebraker dans ce livre : http://www.amazon.com/Readings-Database … 0262693143
A +
Frédéric Brouard, alias SQLpro, ARCHITECTE DE DONNÉES, Expert langage SQL
Le site sur les SGBD relationnel et langage SQL : http://sqlpro.developpez.com/
Modélisation de données, conseil, expertise, audit, optimisation, tuning, formation
* * * * * Enseignant CNAM PACA, ISEN Toulon, CESI Aix en Provence * * * * *
Hors ligne