Re: Looking for a way to implement SQL-92 Domain

Following up on John Bossert, 24 Mar 2003:

>
> Then it's pointed out to me that Oracle doesn't permit a check
> constraint to reference another table.

I'm not sure if this is still a restriction or not in 9ir2. I'd recommend a gander at the latest doco.

> Is there a relatively clean way of doing this? I know I could do it
> with triggers, but I'm concerned that I'm merely replacing complexity
> with another form of complexity.

Not at all. Triggers are there precisely for this sort of thing. What you have to avoid is to put code INSIDE the trigger itself. Just put a call to a stored procedure in the trigger and store your code somewhere else.

Domain tables are a very good way of reducing clutter, BTW. Nothing wrong with using them in Oracle with triggers.

Of course, you can ALWAYS generate your views with a join to the domain table using the key as join column, the value as part of your return list and the domain value as a constant. Works a treat. But not directly updatable without some added complexity.

-- 
Cheers
Nuno Souto
wizofoz2k_at_yahoo.com.au.nospam