Bruno Wolff III wrote:
On Wed, May 26, 2004 at 05:13:14 +0200, Andreas <[EMAIL PROTECTED]> 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 [EMAIL PROTECTED] 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. # #================================================== [EMAIL PROTECTED] #
---------------------------(end of broadcast)--------------------------- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
