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 bad a way of doing it as I thought.
EE
Jaime Casanova wrote:
On 11/17/05, Eric E <whalesuit@xxxxxxxxx> 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 ;)
---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster
