சிவகுமார் மா 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