Re: Partial foreign keys, check constraints and inheritance

>maybe you can solve it adding a new col and allow both to contain null values.
>
>if these are not mutually exclusive you can avoid a check if they are
>check that if one has a non-null value other has null...


I did think about that, but I disliked the idea of two fields of nulls for every one full field.... maybe it's not as
bada way of doing it as I thought. 

EE




Jaime Casanova wrote:

>On 11/17/05, Eric E <whalesuit@gmail.com> wrote:
>
>
>>Hi all,
>>   In my database application, I've repeatedly encountered a particular
>>issue, and I'm not sure I'm addressing it well, so I'd like suggestions
>>on how to deal with it.  The problem is that I need something like a
>>partial foreign key - a foreign key where, based on field1, in some rows
>>field1 references table A, and in some rows field1 references tableB.
>>
>>Here's the gist of the design problem.  Say I have a generic product
>>sales database:  products, customers, orders - orders bring together
>>products and customers.  Now I want a table to track problems associated
>>with any of these items; products, customers or orders, and I want to
>>associated each problem with an item in one of the tables.
>>
>>What's the best way to do this?  My immediate reaction is that I want a
>>partial foreign key, but perhaps this is not a good way to go about such
>>a design.  I've also considered using inheritance. I could put all the
>>data fields for problems into a base table, then use separate inherited
>>tables for each of the tables I want to reference with foreign keys.  I
>>avoided inherited tables in version 7.4 because they didn't seem
>>feature-complete.  Finally, there's the option of doing what I do now,
>>which is use a check constraint.
>>
>>Does anyone have ideas on the best way to acheive this behavior?  Ideas
>>and advice would be much appreciated.
>>
>>Cheers,
>>
>>Eric
>>
>>
>>
>
>maybe you can solve it adding a new col and allow both to contain null values.
>
>if these are not mutually exclusive you can avoid a check if they are
>check that if one has a non-null value other has null...
>
>
>
>
>>The check constraint has the distinct
>>downside of making backups and restoration more complex, as it is added
>>during table creation, and not after data load.
>>
>>
>
>after you make pg_dump edit the file delete the check from the create
>table and put it in an alter table add constraint at the end of the
>file...
>
>
>--
>Atentamente,
>Jaime Casanova
>(DBA: DataBase Aniquilator ;)
>
>
>