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$
NEW.acro := upper(NEW.acro);
$mk_upper$ LANGUAGE plpgsql;
CREATE TRIGGER table_1_acro BEFORE INSERT OR UPDATE ON table_1
FOR EACH ROW EXECUTE PROCEDURE mk_upper();
> 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
> table 2
> acro primary key
> OK person that knows what they are doing puts data into table 2
> 2 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
In response to
pgsql-novice by date
|Next:||From: Andreas Kretschmer||Date: 2009-01-11 13:55:14|
|Subject: Re: Adding not null check constaint to list of columns|
|Previous:||From: mltasmaniac||Date: 2009-01-11 02:36:46|
|Subject: Foreign Keys|