Re: Pas de contrainte référentielle vers un champ non-UNIQUE :-(

J'essaie de mettre une contrainte référentielle mais le champ visé n'a
pas été déclaré comme UNIQUE :

essais=> CREATE TABLE Foo(name TEXT NOT NULL);
CREATE TABLE
essais=> CREATE TABLE Bar(truc TEXT, machin TEXT REFERENCES Foo(name));
ERROR: there is no unique constraint matching given keys for referenced table "foo"

[Si Foo(name) est déclaré UNIQUE, cela passe.]

Première question : pourquoi PostgreSQL 8.3 impose t-il cette
contrainte supplémentaire qui ne me semble pas logique ?

Deuxième question : quel contournement utiliser à part abandonner la
sécurité que me fournit REFERENCES ?

Cette limitation vient en partie, à mon sens, du fait que les références
sont gérées dans PostgreSQL via des triggers.
Aussi les triggers sont utilisés lors de la suppression/update dans ta
table Foo et lors de l'insertion dans ta table Bar.
Cela voudrait ainsi dire que lorsque tu supprimes une entrée dans Foo,
le trigger doive vérifier la non-existence dans Bar ainsi que, si
existence il y a, la présence d'un doublon dans Foo autorisant la
suppression.
Techniquement c'est possible mais je pense que ça complexifie les
choses.
D'autre part, conceptuellement, cela revient à faire une relation une
relation n to n. Je ne sais pas si c'est très normal de cette façon là.
Quelqu'un sait si c'est possible sur d'autre base de données (genre
oracle, sql server)

Le contournement pour le coup est relativement simple. Il suffit juste
d'écrire des triggers équivalent à ce que fait PostgreSQL avec le test
tel que je l'ai décris en plus un peu plus haut. Regarde du côté des
contrib pour voir si ça n'existe pas déjà... Mais bon c'est plus des
références Postgresql dans ce cas là...

Voila...
Le Mon, 8 Sep 2008 16:56:35 +0200, Stephane
Bortzmeyer <bortzmeyer(at)nic(dot)fr> a écrit :

> J'essaie de mettre une contrainte référentielle mais le champ visé n'a
> pas été déclaré comme UNIQUE :
>
> essais=> CREATE TABLE Foo(name TEXT NOT NULL);
> CREATE TABLE
> essais=> CREATE TABLE Bar(truc TEXT, machin TEXT REFERENCES
> Foo(name)); ERROR: there is no unique constraint matching given keys
> for referenced table "foo"
>
> [Si Foo(name) est déclaré UNIQUE, cela passe.]
>
> Première question : pourquoi PostgreSQL 8.3 impose t-il cette
> contrainte supplémentaire qui ne me semble pas logique ?
>
> Deuxième question : quel contournement utiliser à part abandonner la
> sécurité que me fournit REFERENCES ?
>

Stephane Bortzmeyer a écrit :
> J'essaie de mettre une contrainte référentielle mais le champ visé n'a
> pas été déclaré comme UNIQUE :
>
> essais=> CREATE TABLE Foo(name TEXT NOT NULL);
> CREATE TABLE
> essais=> CREATE TABLE Bar(truc TEXT, machin TEXT REFERENCES Foo(name));
> ERROR: there is no unique constraint matching given keys for referenced table "foo"
>
> [Si Foo(name) est déclaré UNIQUE, cela passe.]
>
> Première question : pourquoi PostgreSQL 8.3 impose t-il cette
> contrainte supplémentaire qui ne me semble pas logique ?
>

Pourquoi contrainte supplémentaire ? une référence, c'est une clé
étrangère. L'idée de la clé étrangère, c'est qu'*un* élément est lié à
*un ou plusieurs* autres. Ce premier élément doit être dissociable. Et
il doit être dissociable pour que les règles ON UPDATE et ON DELETE des
clés étrangères puissent fonctionner.

Mais si je comprends bien, tu veux faire un système où un ou plusieurs
éléments d'un table sont liés à un ou plusieurs éléments d'une table Y
(du n-n à la place du 1-n de PostgreSQL).

> Deuxième question : quel contournement utiliser à part abandonner la
> sécurité que me fournit REFERENCES ?
>

Il va falloir que tu crées un système de triggers qui fera la même
chose, mais qui n'implémentera pas ON UPDATE et ON DELETE.

--
Guillaume.
http://www.postgresqlfr.org
http://dalibo.com

On Mon, 8 Sep 2008 16:56:35 +0200, Stephane Bortzmeyer <bortzmeyer(at)nic(dot)fr>
wrote:

Bonjour,

> J'essaie de mettre une contrainte référentielle mais le champ visé n'a
> pas été déclaré comme UNIQUE :
>
> essais=> CREATE TABLE Foo(name TEXT NOT NULL);
> CREATE TABLE
> essais=> CREATE TABLE Bar(truc TEXT, machin TEXT REFERENCES Foo(name));
> ERROR: there is no unique constraint matching given keys for referenced
> table "foo"
>
> [Si Foo(name) est déclaré UNIQUE, cela passe.]
>
> Première question : pourquoi PostgreSQL 8.3 impose t-il cette
> contrainte supplémentaire qui ne me semble pas logique ?

Ce n'est pas Pg 8.3, c'est la définition même de l'intégrité
référentielle¹. C'est dans la doc de PostreSQL depuis au moins la
version 7.3, je ne suis pas remonté plus loin.

>
> Deuxième question : quel contournement utiliser à part abandonner la
> sécurité que me fournit REFERENCES ?
>

Des triggers ?

¹ : http://en.wikipedia.org/wiki/Foreign_key

--
Sébastien

On Mon, Sep 08, 2008 at 05:16:30PM +0200,
Jean-Samuel Reynaud <reynaud(at)elma(dot)fr> wrote
a message of 43 lines which said:

> Cette limitation vient en partie, à mon sens, du fait que les
> références sont gérées dans PostgreSQL via des triggers.

Et donc ce serait une limite d'implémentation et pas un problème
fondamental d'algèbre relationnelle ?

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Stephane Bortzmeyer a écrit :
> On Mon, Sep 08, 2008 at 05:16:30PM +0200,
> Jean-Samuel Reynaud <reynaud(at)elma(dot)fr> wrote
> a message of 43 lines which said:
>
>> Cette limitation vient en partie, à mon sens, du fait que les
>> références sont gérées dans PostgreSQL via des triggers.
>
> Et donc ce serait une limite d'implémentation et pas un problème
> fondamental d'algèbre relationnelle ?
>
>

J'ai du mal à comprendre pourquoi il y a discussion. Comment faire référence à
une ligne qui peut ne pas être unique ?
Une colonne ne peut pas faire référence à une valeur de colonne dans une ligne
qui, elle, peut ne pas être unique.

On parle bien de référence ici, et non de valeur.

- --
Stéphane Schildknecht
PostgreSQLFr : http://www.postgresql.fr

Venez nous rencontrer le 4 octobre lors du plus important événement
PostgreSQL francophone : http://www.pgday.fr

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.6 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org

iD8DBQFIxnDqA+REPKWGI0ERAperAJwNrhHuPEwmCR/gImjj0MX+VEBynACgySUY
ULycSqYWvLOePT6QUxUgKIQ=
=rFzq
-----END PGP SIGNATURE-----

On Tue, 9 Sep 2008 13:54:10 +0200, Stephane Bortzmeyer <bortzmeyer(at)nic(dot)fr>
wrote:
>
>
>> Cette limitation vient en partie, à mon sens, du fait que les
>> références sont gérées dans PostgreSQL via des triggers.
>
> Et donc ce serait une limite d'implémentation et pas un problème
> fondamental d'algèbre relationnelle ?

Une clé étrangère est une référence à un enregistrement, et non pas
à une valeur. L'enregistrement est identifié au moins pas un clé unique,
ou mieux encore par un clé primaire.

Ca fait bien parti des définitions fondamentales des bases de données
relationelles, ça n'est pas un problème d'implémentation dans
PostgreSQL.

--
Sébastien

Le Tue, 09 Sep 2008 14:49:46 +0200,
"Stéphane A. Schildknecht" <stephane(dot)schildknecht(at)postgresqlfr(dot)org> a
écrit :
> Stephane Bortzmeyer a écrit :
> > On Mon, Sep 08, 2008 at 05:16:30PM +0200,
> > Jean-Samuel Reynaud <reynaud(at)elma(dot)fr> wrote
> > a message of 43 lines which said:
> >
> >> Cette limitation vient en partie, à mon sens, du fait que les
> >> références sont gérées dans PostgreSQL via des triggers.
> >
> > Et donc ce serait une limite d'implémentation et pas un problème
> > fondamental d'algèbre relationnelle ?
> >
> >
>
> J'ai du mal à comprendre pourquoi il y a discussion. Comment faire
> référence à une ligne qui peut ne pas être unique ?
> Une colonne ne peut pas faire référence à une valeur de colonne dans
> une ligne qui, elle, peut ne pas être unique.
>
> On parle bien de référence ici, et non de valeur.
>
Effectivement, je suis d'accord avec toi, c'est la définition de la
notion.

Si l'effet que tu souhaites est différente de cette notion, tu
peux la définir avec des triggers en base...

On Tue, Sep 09, 2008 at 03:43:49PM +0200,
Sébastien Lardière <sebastien(at)lardiere(dot)net> wrote
a message of 29 lines which said:

> Une clé étrangère est une référence à un enregistrement, et non pas
> à une valeur.

OK, comme cela, je comprends. Merci.

Mon but initial était de forcer une valeur à être présente dans une
autre table (pas forcément de manière unique) et, pour cela, la seule
solution est l'usage de « triggers » (en s'inspirant du message de
Jean-Samuel Reynaud). Correct ?

On Wed, 10 Sep 2008 12:08:25 +0200, Stephane Bortzmeyer <bortzmeyer(at)nic(dot)fr>
wrote:
>
>> Une clé étrangère est une référence à un enregistrement, et non
> pas
>> à une valeur.
>
> OK, comme cela, je comprends. Merci.
>
> Mon but initial était de forcer une valeur à être présente dans une
> autre table (pas forcément de manière unique) et, pour cela, la seule
> solution est l'usage de « triggers » (en s'inspirant du message de
> Jean-Samuel Reynaud). Correct ?
>

Oui, avec un trigger, tu va pouvoir faire ce que tu veux, y compris
verifier la présence d'une valeur, et annuler la transation en cours le
cas échéant.

--
Sébastien Lardière