Re: Check constraint violated

From: Ed Prochak <edprochak_at_adelphia.net>
Date: Mon, 16 Jun 2003 01:34:15 GMT
Message-ID: <3EED22FF.30303@adelphia.net>

Me wrote:

> On Sat, 14 Jun 2003 16:08:46 GMT,  Ed Prochak wrote:
> 
> 

>>Me wrote:
>>
>>>On Sat, 14 Jun 2003 11:33:48 +0000, andrewst wrote:
>>>
>>>
>>>
>>>>Originally posted by Me
>>>>
>>>>
>>>>>On Fri, 13 Jun 2003 21:33:43 +0530, Me wrote:
>>>>>
>>>>>
>>>>>>2) How can I put a check constraint that allows entering data
>>>>>
>>>>> into apointee only
>>>>>
>>>>>
>>>>>>if the nominee's age is lesser than 18?
>>>>>
>>>>>Could someone help me with this constraint.
>>>>
>>>>ALTER TABLE apointee
>>>>ADD CONSTRAINT chk_nominee_age CHECK (age < 18);
>>>
>>>
>>>No, that would not work. What I mean is this:
>>>There are two separate relations by name nominee and apointee; a policy holder
>>>will always have a nominee, if the nominee is a minor only then he will have an
>>>apointee whose age _will_ have to be greater than 18.
>>>
>>>At the same time the proposer's nominee's age must be lesser than 18 (minor)
>>>
>>>Thanks.
>>>
>>
>>Well, your requirement doen't sound like a check constraint, but a higher
>>level business rule. You need to enforce it in the application layer.
>>Constraints cannot solve ALL these problems, and you don't want to be changing
>>the DB each time a rule like this changes.

> 
> 
> I am validating this from VB but I wanted to know if I could do this form SQL as
> well.
> 
> Thanks.

Well, I didn't say you could not do it in the Database, I just said you cannot do it in a constraint. Have you considered adding triggers (PL/SQL) to check rules like this? If at all possible I suggest you make them table driven, but you may be happy with just a simple BEFORE INSERT trigger.

But it you already validated this, why add it to the DB? Likely you intend to remove the VB validation, but then you will have to add code to handle bad inserts (which return errors when the rule is violated).

HTH,

-- 
Ed Prochak
running    http://www.faqs.org/faqs/running-faq/
netiquette http://www.psg.com/emily.html
--
"Two roads diverged in a wood and I
I took the one less travelled by
and that has made all the difference."
robert frost

Received on Sun Jun 15 2003 - 20:34:15 CDT