Skip site navigation (1) Skip section navigation (2)

Re: Multitable uniqueness ?

From: Jan Wieck <JanWieck(at)Yahoo(dot)com>
To: Bruno Wolff III <bruno(at)wolff(dot)to>
Cc: Andreas <maps(dot)on(at)gmx(dot)net>, pgsql-sql(at)postgresql(dot)org
Subject: Re: Multitable uniqueness ?
Date: 2004-05-26 13:56:17
Message-ID: 40B4A201.1090405@Yahoo.com (view raw or flat)
Thread:
Lists: pgsql-sql
Bruno Wolff III wrote:

> On Wed, May 26, 2004 at 05:13:14 +0200,
>   Andreas <maps(dot)on(at)gmx(dot)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(at)postgresql(dot)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(at)Yahoo(dot)com #


In response to

pgsql-sql by date

Next:From: Jean-Luc LachanceDate: 2004-05-26 14:00:29
Subject: Re: Multitable uniqueness ?
Previous:From: Bruno Wolff IIIDate: 2004-05-26 12:20:13
Subject: Re: Multitable uniqueness ?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group