From: Bruno Wolff III <bruno@wolff.to>
To: Andreas <maps.on@gmx.net>
Cc: pgsql-sql@postgresql.org
Subject: Re: Multitable uniqueness ?
Date: Wed, 26 May 2004 07:20:13 -0500
Message-ID: <20040526122013.GA472@wolff.to> (raw)
In-Reply-To: <40B40B4A.4050908@gmx.net>
References: <40B40B4A.4050908@gmx.net>
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.
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.
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: bruno@wolff.to, maps.on@gmx.net
Subject: Re: Multitable uniqueness ?
In-Reply-To: <20040526122013.GA472@wolff.to>
* 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