Thursday, May 5, 2011

sql server 2005 :how to put not null constraint on a column depending upon value in other column?

in a table two of columns are billable(bit),billabledate(datetime).i want billable date to be not null if billable is not null.

From stackoverflow
  • I'd try adding a trigger to the table, on after insert and after update, to enforce that constraint. Check the billable value, and block insert/update in case it is not null and the billabledate is null.

  • Add a check constraint:

    CHECK (billable is not null and billabledate is not null) OR (billable is null)

  • You need a Check Constraint

    ALTER TABLE dbo.Table WITH NOCHECK
    ADD CONSTRAINT CK_Table_BusinessRule CHECK (Billable IS NOT NULL AND BillableDate IS NOT NULL)

    http://msdn.microsoft.com/en-us/library/ms179491(SQL.90).aspx

0 comments:

Post a Comment

Note: Only a member of this blog may post a comment.