Home Forums SQL Server 2008 T-SQL (SS2K8) How to restrict insertion depends on condition? RE: How to restrict insertion depends on condition?

  • August 19, 2013 at 1:04 am

    #1642555

    First: define the smallest datatype possible for all of your columns. So you probably should change the ID to an INT datatype and the Lodging, FoodBill and DailyAllowance to a DECIMAL(10,2) datatype. Defining them all as VARCHAR(10) will not be the best practice and will give you lot of problems with implicit and explicit conversions.

    Second: does the requirement state you have to use NULL or are you allowed to use 0?

    You could define a CHECK CONSTRAINT on the table with something like "((Lodging + FoodBill) = 0 AND DailyAllowance > 0) OR ((Lodging + FoodBill) > 0 AND DailyAllowance = 0)"

    ** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **