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)