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
---------------------------(end of broadcast)--------------------------- TIP 6: Have you searched our list archives?
http://archives.postgresql.org
