constraint question

[Jesse]

I am new to the SQL server world. Hope someone will kindly give me answer
to the following situation. Thanks in advance.

Among many there are 3 columns in a table:
ExemptEmployee (bit, Not Null)
AnnualSalary (Money, Null)
HourlyWage (Money, Null)

If ExemptEmployee is True, then AnnualSalary field must have a value and
HourlyWage field must be Null. If ExemptEmployee is False, then AnnualSalary
field must be Null and HourlyWage field must have a value.

Is there any easy way of enforcing the logic using constraint? If so, how
would write the constraint?


Jesse

[Michael Levy]

I used this:

create table t (
ExemptEmployee bit Not Null,
AnnualSalary Money Null,
HourlyWage Money Null,

constraint validate
check ((ExemptEmployee = 1 and AnnualSalary is not null and HourlyWage is
null) or
(ExemptEmployee = 0 and AnnualSalary is null and HourlyWage is not null))
)

-Mike
--
Michael Levy MCDBA, MCSD, MCT
michaell@gasullivan.com