constraint question


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 2 of 2

Thread: constraint question

  1. #1
    Jesse Guest

    constraint question


    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

  2. #2
    Michael Levy Guest

    Re: constraint question

    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



Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center