Re: Function Based Foreign Key??

danths_at_spryinc.com (sd) wrote in message news:<27446b9b.0205060900.25bb1bec_at_posting.google.com>...
> Hi All,
> I have implemented "function based indexes" in the past but is
> there any such concept as function based constraints/foreign key.
> Ofcourse such a validation could always be implemented thru a trigger
> but I was trying to avoid a trigger.
> In the past in a particular table the zip code was being stored as 5
> characters which was then referenced against a zip code table ( 5
> characters zip code) to determine the timezone, now I need to store
> 5+4 format of the zip code am bit skeptical about dropping the
> constraint lest it should break some other code.
>
> Any suggestions.
>
> Thanks
> sd

I would suggest you create an artificial key and use that instead of the zip code as an FK. I suggest you never use user data as a PK since it can change. This will mitigate future problems.

If thats not an option then you could create a multi-column foreign key so that child_table(zip5,zip4) references zip_table(zip5,zip4).

-Peter Received on Tue May 07 2002 - 00:56:43 CEST