check constraint expression prob

• eamonroche

  SSCommitted

  Points: 1678

  More actions

  January 12, 2004 at 9:08 am

  #84007

  I am trying to add a check constraint to my table:

  alter table ads

  ADD CONSTRAINT

  CK_ads_1 (not([ad_status] = 'approved, awaiting artwork files' and [approved_snapshot_id] = null))

  ie approved_snapshot_id cant be null when the ad_status is 'approved, awaiting artwork files'

  the constraint seems to 'save' properly, however the constraint just doesn't work. Is it because constraints ignore columns with null values? If so, is there an alternative way of solving my problem. Thanks.

• rockmoose

  SSCarpal Tunnel

  Points: 4571

  More actions

  January 13, 2004 at 2:24 am

  #489696

  try "is null" instead of "= null"

  alter table ads

  ADD CONSTRAINT

  CK_ads_1 (not([ad_status] = 'approved, awaiting artwork files' and [approved_snapshot_id] is null))

  if( null = null )

      print 'null = null'

  if( null is null )

      print 'null is null'

  /rockmoose

  
  You must unlearn what You have learnt

• eamonroche

  SSCommitted

  Points: 1678

  More actions

  January 13, 2004 at 3:07 am

  #489697

  thanks rockmoose, works fine now