Обсуждение: Foreign Keys
Hi List, Ok I apologize if this is obvious etc but ..... 2 tables (trying to keep question simple) table 1 tbl_id primary key tbl2_acro foreign key table2.acro other_col table 2 acro primary key OK person that knows what they are doing puts data into table 2 acro Nm aB fiL 2 Ac aC ok heres the thing if someone now tries to put data into table 1 tbl2_acro must exist in table 2. My problem is how can I get it accept the tbl2_acro typed as eg nM, Nm or NM but still return Nm. As naturally if you type nM or NM or nm it returns a foreign key violation. Is this possible from the dbase level or is it more the front end level? As various users will have various ways of tying (naturally). Does this make sense? :) Peter Jackson
I am not sure I know of an elegant solution to this, but I can think of two possible solutions. One is to change the primary key on table 2 to a serial type, and place a unique constraint on the acro column. Then change your foreign key in table 1 to an integer type. The problem is you have to do a little more work in your user interface, and you are forced to always perform a join on table 2 to find out the value of acro. In table 2 you could add a second column called acro_uppercase that contains the same data in all uppercase. Then point your foreign key in table 1 to that column, then add a trigger to table 1 to convert the typed in data to upper case. Only thing is now if you want to display the acro in a user friendly way you are forced to perform a join against table 2. The trigger could look something like this (if you go this route you might also want to use the trim() function as well): CREATE FUNCTION mk_upper() RETURNS trigger AS $mk_upper$ BEGIN NEW.acro := upper(NEW.acro); RETURN NEW; END; $mk_upper$ LANGUAGE plpgsql; CREATE TRIGGER table_1_acro BEFORE INSERT OR UPDATE ON table_1 FOR EACH ROW EXECUTE PROCEDURE mk_upper(); http://www.postgresql.org/docs/8.3/static/plpgsql-trigger.html mltasmaniac@tasjackson.com wrote: > Hi List, > > Ok I apologize if this is obvious etc but ..... > > 2 tables (trying to keep question simple) > > table 1 > tbl_id primary key > tbl2_acro foreign key table2.acro > other_col > > table 2 > acro primary key > > OK person that knows what they are doing puts data into table 2 > > acro > Nm > aB > fiL > 2 Ac > aC > > > ok heres the thing if someone now tries to put data into table 1 tbl2_acro > must exist in table 2. My problem is how can I get it accept the tbl2_acro > typed as eg nM, Nm or NM but still return Nm. As naturally if you type nM > or NM or nm it returns a foreign key violation. Is this possible from > the dbase level or is it more the front end level? As various users will > have various ways of tying (naturally). > > Does this make sense? :) > > Peter Jackson > > > -- David Gardner
David Gardner wrote: > I am not sure I know of an elegant solution to this, but I can think > of two possible solutions. > > One is to change the primary key on table 2 to a serial type, and > place a unique constraint on the acro column. Then change your foreign > key in table 1 to an integer type. The problem is you have to do a > little more work in your user interface, and you are forced to always > perform a join on table 2 to find out the value of acro. > > In table 2 you could add a second column called acro_uppercase that > contains the same data in all uppercase. > Then point your foreign key in table 1 to that column, then add a > trigger to table 1 to convert the typed in data to upper case. > Only thing is now if you want to display the acro in a user friendly > way you are forced to perform a join against table 2. The trigger > could look something like this (if you go this route you might also > want to use the trim() function as well): > > CREATE FUNCTION mk_upper() RETURNS trigger AS $mk_upper$ > BEGIN > NEW.acro := upper(NEW.acro); > RETURN NEW; > END; > $mk_upper$ LANGUAGE plpgsql; > > CREATE TRIGGER table_1_acro BEFORE INSERT OR UPDATE ON table_1 > FOR EACH ROW EXECUTE PROCEDURE mk_upper(); > > http://www.postgresql.org/docs/8.3/static/plpgsql-trigger.html > > Ah well guess it was to much to hope for. Will have to sit down and think of the way to handle it now. Thought I'd be pushing my luck. Only draw back with above options the acro's can be used in several places and some times the way they are outputted matter and sometimes it doesnt. (in the outputted format). Well guess I can always fall back to the good old 4x2 option to train them to type it in correctly. Peter Jackson (apologizes to David I never meant to send it direct to you)