Re: implement a referential integrity constraint (long)

On May 8, 10:51 pm, --CELKO-- <jcelko..._at_earthlink.net> wrote:
> >> This isn't the way I would have done it,  ... <<
>
> That is being kind :)
>
> Try the "CHECK()-from-hell approach"
>
> >> ..restricted to a list of countries in the world.  <<
>
> As opposed to those countries that are somewhere else? :)
>
>  CONSTRAINT GenralAddressValidation
> CHECK( CASE county_code
>               WHEN  'xx'
>               THEN (CASE WHEN line1 NOT LIKE ..THEN 'F'
>                                     WHEN line2 NOT LIKE ..THEN 'F'
>                                      ..END)
>               WHEN  'yy'
>               THEN (CASE WHEN line1 NOT LIKE ..THEN 'F'
>                                     WHEN line NOT LIKE ..THEN 'F'
>                                      ..END)
>              .. ELSE 'T' END = 'T')
>
> This is a pain to write, but the idea is to give each country its own
> second level CASE expression.

First, I got to say this...
> >> ..restricted to a list of countries in the world. <<
> As opposed to those countries that are somewhere else? :)
Yup. You could restrict the selection to the list of countries in Asia, Europe and the Indian sub-continent! :-p I suppose countries in the world == countries in the universe. That's an aside.

Second, the check from hell sound interesting, and I have no doubt that's the way CJ Date would've done it! At first I thought you were joking! I might have to use something a bit more sophisticated than a case statement though. NB: I don't have country code either.

How about:
CONSTRAINT GeneralAddressValidation CHECK( f( line1, line1_label ) = true AND f( line2, line2_label ) = true AND ... )

where f( v_line, v_label ) is
if v_label = 'Country' then
  return( v_line1 in (country_list) )
else
  return( true )
end if;

That would be equivalent to what you've put. No doubt that'll get a few comments from the forum. Received on Tue May 12 2009 - 05:57:28 CEST