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