Re: Referential integrity using constant in foreign key

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