From: | "Albe Laurenz" <laurenz(dot)albe(at)wien(dot)gv(dot)at> |
---|---|
To: | சிவகுமார் மா *EXTERN* <masivakumar(at)gmail(dot)com>, <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: Enforcing Join condition |
Date: | 2007-11-15 14:02:13 |
Message-ID: | D960CB61B694CF459DCFB4B0128514C28E32E5@exadv11.host.magwien.gv.at |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
சிவகுமார் மா 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
From | Date | Subject | |
---|---|---|---|
Next Message | Sam Mason | 2007-11-15 14:02:25 | Re: Chunk Delete |
Previous Message | Alexander Staubo | 2007-11-15 13:46:09 | Re: Chunk Delete |