On Jul 14, 5:02 pm, "lenygold via DBMonster.com" <u41482@[EMAIL PROTECTED]
> wrote:
> Thank you Lennart.
> I like this approach.
> One more question. What if you a manager or superviser then
commissions
> always should be zero and education level more then 10.
In general, DB2 does not sup****t sub selects in check constraints, but
as long as the rule regards a relation****p between columns in the same
row, it is a good candidate for a check constraint (it is not possible
at all times though). If I understand your table this case should be
something like (I assume supervisor and manager belongs to the JOB
domain) :
ALTER TABLE EMP_SCREEN_EDIT ADD CONSTRAINT <NAME>
CHECK ( NOT ( JOB IN ('manager', 'superviser')) OR ( COMM = 0 AND
EDLEVEL > 10 ) )
If you are a manager or supervisor, [ NOT ( JOB IN ('manager',
'superviser')) ] evaluates to FALSE, hence [ (commissions = 0 AND
EDLEVEL > 10 ) ] must evaluate to TRUE for the constraint to evaluate
to TRUE.
A number of the attributes in the table are nullable and you have to
be extra careful with these (but that holds whether you put them in a
trigger or in a constraint). For example, what does it mean if COMM is
null, is that ok for a supervisor?
/Lennart
|
