Re: Referential integrity using constant in foreign key

From: Richard Huxton <dev(at)archonet(dot)com>
To: "Thomas F(dot)O'Connell" <tfo(at)sitening(dot)com>
Cc: Andrus Moor <nospameetasoftnospam(at)online(dot)ee>, pgsql-general(at)postgresql(dot)org
Subject: Re: Referential integrity using constant in foreign key
Date: 2005-03-29 13:33:09
Message-ID: 42495915.9020306@archonet.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Thomas F.O'Connell wrote:
> Referential integrity never dictates the need for "dummy" columns. If
> you have a column that you need to refer to a column in another table so
> strongly that you want the values always to be in sync, you create a
> foreign key, establishing referential integrity between a column (or
> columns) in the table with the foreign key and a column in another table
> (usually a primary key).
>
> I don't understand what you're trying to accomplish well enough to be
> able to make a specific recommendation based on your examples that suits
> your needs.

I know what he's trying to do, because I do it myself. And the short
answer Andrus is "no, there is no shortcut".

The typical usage is something like:

CREATE TABLE contract (con_id int PRIMARY KEY, con_type varchar,
con_date ...)
CREATE TABLE purchase_details (con_id int, item_id int, qty int, ...)
CREATE TABLE rental_details (con_id int, rental_period interval, ...)

Now, you only want purchase_details to reference rows in contract where
con_type="purchase". Likewise rental_details should only reference rows
with con_type="rental".

We can't reference a view, and we can't add a constant to the
foreign-key definition. So, the options are:

1. Don't worry about it (not good design).
2. Add a "dummy" column to purchase_details which only contains the
value "purchase" so we can reference the contract table (wasteful)
3. Write your own foreign-key triggers to handle this (a fair bit of work)
4. Eliminate the con_type column and determine it from what tables you
join to. But that means you now need to write a custom constraint across
all the xxx_details tables so that you don't get a mixed purchase/rental
table.

None of these are very attractive, but that's where we stand at the moment.

HTH
--
Richard Huxton
Archonet Ltd

In response to

Browse pgsql-general by date

  From Date Subject
Next Message Sean Davis 2005-03-29 14:01:24 Re: Views!
Previous Message Hrishikesh Deshmukh 2005-03-29 13:27:36 Views!