Re: Foreign Keys

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(at)tasjackson(dot)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