From: Jan Wieck <JanWieck@Yahoo.com>
To: Bruno Wolff III <bruno@wolff.to>
Cc: Andreas <maps.on@gmx.net>
Cc: pgsql-sql@postgresql.org
Subject: Re: Multitable uniqueness ?
Date: Wed, 26 May 2004 09:56:17 -0400
Message-ID: <40B4A201.1090405@Yahoo.com> (raw)
In-Reply-To: <20040526122013.GA472@wolff.to>
References: <40B40B4A.4050908@gmx.net>
<20040526122013.GA472@wolff.to>
Bruno Wolff III wrote:
> On Wed, May 26, 2004 at 05:13:14 +0200,
> Andreas <maps.on@gmx.net> wrote:
>>
>> Is there a way to have something like this : UNIQUE (table_1.id,
>> table_2.xxx)
>
> Postgres doesn't support database constraints at this time which is
> what you would need to do this simply.
>
> You can enforce this constraint by creating a third table that has the
> ids, id type and a separate id type field for each possible type that
> will be null except for the type field that matches the actual type.
> You also need to add a type field to the original two tables. Then you
> set up a composite foreign key from the new table to each of the per type
> tables using the id and the matching id type fields. Each per type table
> should have a foriegn key refernce for id to the combined table.
> This will enforce a 1-1 relationship between the combined table and the
> union of the per type tables.
I think a third table with just the unique id plus custom triggers on
both tables that insert/update/delete the id into/from the third table
are totally enough.
Jan
>
> It is probably possible to get rid of the redundant copies of the field type
> using triggers, but I don't know that there is much of a benefit to doing so.
> The redundant values will all be kept in sync with constraints, so there isn't
> a data consistancy problem with doing it that way. This method is going to be
> more portable than using triggers. This method will probably be within a
> constant factor as efficient as anything you do with triggers.
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly
--
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#================================================== JanWieck@Yahoo.com #
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: JanWieck@Yahoo.com, bruno@wolff.to, maps.on@gmx.net
Subject: Re: Multitable uniqueness ?
In-Reply-To: <40B4A201.1090405@Yahoo.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