Thread: Enforcing Join condition
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
INSERT INTO test (info_type, info_reference) values ('def','103');
--- 103 from tableB
INSERT INTO test (info_type, info_reference) values ('def','104');
--- 104 from tableB
INSERT INTO test (info_type, info_reference) values ('def','105');
--- 105 from tableB
INSERT INTO test (info_type, info_reference) values ('def','111');
--- 111 from tableB
Now when joining tableA or tableB with test, joining only
info_reference will be wrong, we should also mention the info_type
value.
1. Is this an appropriate design for this requirement?
2. Is there a way to enforce the dual condition checking on all
queries. If a join is made to info_reference, info_type should also be
specified?
Thanks.
Ma Sivakumar
--
மா சிவகுமார்
எல்லோரும் எல்லாமும் பெற வேண்டும்
http://masivakumar.blogspot.com
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
சிவகுமார் மா wrote:
> 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
> INSERT INTO test (info_type, info_reference) values ('def','103');
> --- 103 from tableB
> INSERT INTO test (info_type, info_reference) values ('def','104');
> --- 104 from tableB
> INSERT INTO test (info_type, info_reference) values ('def','105');
> --- 105 from tableB
> INSERT INTO test (info_type, info_reference) values ('def','111');
> --- 111 from tableB
>
> Now when joining tableA or tableB with test, joining only
> info_reference will be wrong, we should also mention the info_type
> value.
>
> 1. Is this an appropriate design for this requirement?
> 2. Is there a way to enforce the dual condition checking on all
> queries. If a join is made to info_reference, info_type should also be
> specified?
I think I understand:
You want to have a "conditional" foreign key reference that checks
against different tables depending on a "type" field, right?
For complicated conditions like this, you could use a
BEFORE INSERT trigger that throws an error when the condition
is violated.
Yours,
Laurenz Albe
On Nov 15, 2007 5:52 PM, Sam Mason <sam@samason.me.uk> wrote: > 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)) > ); Thanks for sharing. Will try it. But, we have more than 10 types in one table. Will be tough to handle. Ma Sivakumar -- மா சிவகுமார் எல்லோரும் எல்லாமும் பெற வேண்டும் http://masivakumar.blogspot.com
On Nov 15, 2007 7:32 PM, Albe Laurenz <laurenz.albe@wien.gv.at> wrote: > > You want to have a "conditional" foreign key reference that checks > against different tables depending on a "type" field, right? > > For complicated conditions like this, you could use a > BEFORE INSERT trigger that throws an error when the condition > is violated. That we do. My query is to put a check on select queries to avoid programmers not using all the necessary conditions. Thanks and regards, Ma Sivakumar -- மா சிவகுமார் எல்லோரும் எல்லாமும் பெற வேண்டும் http://masivakumar.blogspot.com
சிவகுமார் மா wrote: >> You want to have a "conditional" foreign key reference that checks >> against different tables depending on a "type" field, right? >> >> For complicated conditions like this, you could use a >> BEFORE INSERT trigger that throws an error when the condition >> is violated. > > That we do. My query is to put a check on select queries to avoid > programmers not using all the necessary conditions. You can create a view that does the join the correct way (maybe a UNION of two different joins) and give the programmers access to that view, but not to the underlying tables. Yours, Laurenz Albe
On Thu, Nov 15, 2007 at 07:46:46PM +0530, ??????????????????????????? ?????? wrote: > On Nov 15, 2007 5:52 PM, Sam Mason <sam@samason.me.uk> wrote: > > 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)) > > ); > > Thanks for sharing. Will try it. But, we have more than 10 types in > one table. Will be tough to handle. As Albe suggested, a view is about all that's going to help the poor people who work with this. When I do this sort of thing, I tend to find that there are very few queries that actually need everything all together in one place. It's generally that (using the naming above) that you'd do a query on "table1", "test" and something that references "test". Queries that reference "test", "table1" and "table2" are reasonably rare. Of course, it could be that I was just lucky here. Sam
On Nov 15, 2007 8:17 PM, Sam Mason <sam@samason.me.uk> wrote: > As Albe suggested, a view is about all that's going to help the poor > people who work with this. When I do this sort of thing, I tend to > find that there are very few queries that actually need everything all > together in one place. True in our case also. > It's generally that (using the naming above) > that you'd do a query on "table1", "test" and something that references > "test". Queries that reference "test", "table1" and "table2" are > reasonably rare. Of course, it could be that I was just lucky here. Now I know we are not alone handling these type of tables :-) Best regards, Ma Sivakumar -- மா சிவகுமார் எல்லோரும் எல்லாமும் பெற வேண்டும் http://masivakumar.blogspot.com