Thread: 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)