Kevin Hunter wrote:
[...]
> I originally had him code his project for Postgres, but for reasons
> beyond our control we've had to move to Oracle. In designing the schema
> we have need of a constraint that checks values in other tables. The
> way that I currently know how to do this in Postgres is with PLpgSQL
> functions. Then I add something like
>
> CONSTRAINT away_team_is_playing CHECK ( NOT teamIsPlaying( awayteamid,
> timeid ) )
>
> to the table schema. No big deal, except that it seems Oracle can't use
> anything other than a simple column constraint. He can't use any custom
> functions like he could in Postgres, and we've yet to find a solution to
> do what he needs.
well doing it that way is usually not a good idea at all (you cannot
actually use arbitrary queries in a CHECK constraint in pg either -
using a function to hide that is cheating the database - oracle might
actually be more(!) clever here not less ...). this why you can get into
all kind of weird situations with losing the integrity of your data or
running into serious issues during dump/restore for example.
What you need to do here is to use a trigger.
Stefan
