From: Dirk Jagdmann <jagdmann@gmail.com>
To: Jamie Tufnell <diesql@googlemail.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: Composite UNIQUE across two tables?
Date: Wed, 12 Mar 2008 23:16:59 -0700
Message-ID: <5d0f60990803122316v30c994cbm3b77a1c2c879d158@mail.gmail.com> (raw)
In-Reply-To: <b0a4f3350803121942k533d5028s9b94a05aea00695e@mail.gmail.com>
References: <47D100E0.A3DD.0030.0@indicator.be>
<20080307083421.26A9D700009D@mwinf2f21.orange.fr>
<47D110C0.A3DD.0030.0@indicator.be>
<b0a4f3350803070746w17c7c29dhdef57ada7568cdc0@mail.gmail.com>
<b0a4f3350803090827p61b075c0rd27e63c2cb23ab93@mail.gmail.com>
<5d0f60990803111203m18345c5ata6f4d407867ed60d@mail.gmail.com>
<b0a4f3350803121942k533d5028s9b94a05aea00695e@mail.gmail.com>
> > My advise would be to have two triggers for insert/update on the site
> > and users table that check the uniqueness of the username with the
> > site_group. A have made some tests with inserts and updates on the
> > existing users and sites and these two functions seem to work.
>
> I think this is the way that I'll go. I'd hoped to somehow express this
> solely in the design, if you know what i mean (e.g. without writing
> SPs), but it looks like this is the best way to do it.
Well I thought about that, but finally came to the conclusion, that
standard SQL constraints can not express this inter-table
relationships. As I'm not a fan of (artificially) breaking up tables I
just wrote those two pl/pgsql functions, because I'd rather have a
simple table design and some complicated constraint checking functions
than the other way.
> Thank you for your taking the time to write this up, it's very much
> appreciated.
Most people reading this list like to think about/learn from other
people's problems.
--
---> Dirk Jagdmann
----> http://cubic.org/~doj
-----> http://llg.cubic.org
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: jagdmann@gmail.com, diesql@googlemail.com
Subject: Re: Composite UNIQUE across two tables?
In-Reply-To: <5d0f60990803122316v30c994cbm3b77a1c2c879d158@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox