Unique constraint that ignores nulls

• Tim Chapman-309587

  SSC Rookie

  Points: 28

  More actions

  June 19, 2007 at 4:20 am

  #153639

  Can anyone help me achieve this. I want to enforce a constraint across 2 columns but that constraint will not be enforced if one of the values is NULL.

  table columns: ID int, PROGRAMID int, USERNAME varchar(50).

  Every record must have a PROGRAMID but any record can have USERNAME NULL. However the combination of PROGRAMID and a non NULL USERNAME must be unique.

  How can I achieve this without using triggers or other higher level logic.

• Ken McKelvey

  SSCoach

  Points: 18843

  More actions

  June 19, 2007 at 4:37 am

  #713764

  Two common ways of achieving this are:

  1. Indexed View

  Create a View which includes WHERE UserName IS NOT NULL and put an unique index on the view.

  2. Calculated Column

  Create a calculated column like ISNULL(UserName, CAST([ID] AS varchar(50)) AS CalcColumn.

  An unique index can then be created on ProgramID, CalcColumn.