Re: Enforcing Join condition

On Thu, Nov 15, 2007 at 01:24:04PM +0530, ??????????????????????????? ?????? wrote:
> Is there a way to force join conditions in queries i.e. When a join is
> made to a table on a particular field, another column should also be
> checked?
>
> CREATE TABLE test (info_type varchar(3), info_reference integer);
> (depending on info_type, info_reference will contain key values from
> different tables)
>
> INSERT INTO test (info_type, info_reference) values ('abc','111'); --- 111 from tableA
> INSERT INTO test (info_type, info_reference) values ('def','101'); --- 101 from tableB
> INSERT INTO test (info_type, info_reference) values ('abc','119'); --- 119 from tableA

What I tend to do here, is something like:

CREATE TABLE test (
type INTEGER,
ref1 INTEGER REFERENCES table1 CHECK ((type = 1) = (ref1 IS NOT NULL)),
ref2 INTEGER REFERENCES table2 CHECK ((type = 2) = (ref2 IS NOT NULL)),
ref3 INTEGER REFERENCES table3 CHECK ((type = 3) = (ref3 IS NOT NULL))
);

yes it means that you get lots of null columns, but PG is reasonably
good about handling them. When you're writing queries that use the
table, then you have to do lots of OUTER JOIN's to get everything you
need together. I asked about this a few weeks ago, but never got any
suggestions about better ways to do things.

Sam