Re: [GENERAL] Enforcing Join condition
On Nov 15, 2007 8:17 PM, Sam Mason [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 1: 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
Re: [GENERAL] 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
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
Re: [GENERAL] Enforcing Join condition
On Nov 15, 2007 7:32 PM, Albe Laurenz [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Enforcing Join condition
On Nov 15, 2007 5:52 PM, Sam Mason [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] Enforcing Join condition
சிவகுமார் மா 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 ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] Enforcing Join condition
On Thu, Nov 15, 2007 at 07:46:46PM +0530, ??? ?? wrote: On Nov 15, 2007 5:52 PM, Sam Mason [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Enforcing Join condition
சிவகுமார் மா 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
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
[GENERAL] 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
---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
