Skip site navigation (1) Skip section navigation (2)

Re: Foreign Keys

From: David Gardner <david(at)gardnerit(dot)net>
To: pgsql-novice(at)postgresql(dot)org
Subject: Re: Foreign Keys
Date: 2009-01-11 06:19:37
Message-ID: 49698F79.3090602@gardnerit.net (view raw or flat)
Thread:
Lists: pgsql-novice
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


In response to

Responses

pgsql-novice by date

Next:From: Andreas KretschmerDate: 2009-01-11 13:55:14
Subject: Re: Adding not null check constaint to list of columns
Previous:From: mltasmaniacDate: 2009-01-11 02:36:46
Subject: Foreign Keys

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group