Re: Foreign key on partial char field

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