Postgres Pro
Downloads
Home   >   mailing lists

Thread: Foreign key on partial char field

Foreign key on partial char field

From
"lmanorders"
Date:
I'm using Postgres 9.2 and connecting to it using libpq.dll on Windows.
I have two tables:
A zip code table with zcode char(5) Primary key, city varchar(30), and state char(2).
 
An employee table that, among other things, contains a zip code field. This field can hold a nine-digit zip code.
I want to add a constraint (foreign key) on the first 5 digits of this zip code that references the zip code table.
I've tried several things, including the following, but I get an error when I try to create the table: 
...
zcode char(9)
CONSTRAINT employee_zipcode_fkey FOREIGN KEY (SUBSTRING(zipcode from 1 for 5))
     REFERENCES zipcds (zipcode) ON UPDATE CASCADE ON DELETE RESTRICT
...
 
Can anyone tell me if this is possible, and if it is, how can this be done?
 
Thanks in advance,
Lynn
 

Re: Foreign key on partial char field

From
Merlin Moncure
Date:
On Wed, Oct 24, 2012 at 6:55 PM, lmanorders <lmanorders@gmail.com> wrote:
> I'm using Postgres 9.2 and connecting to it using libpq.dll on Windows.
> I have two tables:
> A zip code table with zcode char(5) Primary key, city varchar(30), and state
> char(2).
>
> An employee table that, among other things, contains a zip code field. This
> field can hold a nine-digit zip code.
> I want to add a constraint (foreign key) on the first 5 digits of this zip
> code that references the zip code table.
> I've tried several things, including the following, but I get an error when
> I try to create the table:
> ...
> zcode char(9)
> CONSTRAINT employee_zipcode_fkey FOREIGN KEY (SUBSTRING(zipcode from 1 for
> 5))
>      REFERENCES zipcds (zipcode) ON UPDATE CASCADE ON DELETE RESTRICT
> ...
>
> Can anyone tell me if this is possible, and if it is, how can this be done?

It's not directly possible.  FK references take only raw fields, not
expressions.  I would probably consider breaking out the zipcode into
a seprate field, and using a 'before' trigger to populate (taking care
it fires both on insert/update).   Also consider use of left() vs the
stupidly verbose substring().

merlin