On 02/04/10 16:00 +0200, Cédric Krier wrote:
> On 02/04/10 13:56 +0200, Mathias Behrle wrote:
> > * Betr.: " [tryton-dev] Bring some attention on issue1422" (Fri, 2 Apr 2010
> > 11:10:06 +0200):
> >
> > > There is a possible solution that will work only for PostgreSQL:
> > >
> > > CREATE UNIQUE INDEX "index" ON "table" ("name", COALESCE("parent",
> > > 0));
> > >
> > > Is there anybody who has better solution?
> > >
> > >
> > > [1] http://bugs.tryton.org/roundup/issue1422
> >
> > Since it seems, that this issue has to be solved indivdually per DBMS and we
> > don't want to provide our own logical layer, this is the way to go for me.
> >
> > http://dev.mysql.com/doc/refman/5.0/en/create-index.html
> > http://bytes.com/topic/oracle/answers/65985-unique-constraint-null-values
>
> I don't understand what you want to show in those links.
>
> Any way, when looking at the comments in oracle thread, I thought about a
> solution.
> We could add a third integer field with the same value then "parent" but
> without the foreign constraint. It will be filled by COALESCE("parent", -1).
I forgot to mention that integer are not nullable.
> The unique constraint will become:
>
> UNIQUE("name", "parent_")
>
> We could add a constraint to ensure that both values are always sync:
>
> CHECK(COASLESCE("parent", -1) = "parent_")
>
> The "parent_" field will be filled by the override of create and write
> functions.
>
> I know this sounds a little bit hacking but this is my best solution :-)
--
Cédric Krier
B2CK SPRL
Rue de Rotterdam, 4
4000 Liège
Belgium
Tel: +32 472 54 46 59
Email/Jabber: cedric.kr...@b2ck.com
Website: http://www.b2ck.com/
pgpv2ZxaFsrnO.pgp
Description: PGP signature
