Constraint based on the value of a different field

• steven.bird

  Grasshopper

  Points: 21

  More actions

  December 1, 2010 at 11:07 am

  #232069

  I am a first time poster and fairly new to SQL development, so I will attempt to be as clear as possible and hopefully won't be adding a duplicate question.

  Given the following table declaration:

  CREATE TABLE #temp (

  id int primary key identity(1,1) not null

  ,actionId int not null

  ,associatedId int null

  ,CHECK (actionId Like '1' OR actionId Like '2' OR actionId Like '3'))

  I want to add an additional constraint so that if the value of actionId = '3' then associatedId would require a value. Conversely, the constraint would also require that if the value of actionId is '1 or 2' then associatedId would be required to be null.

  First question: Is this possible? If so, the second question is what type of constraint would this be and how would it be implemented?

  Thanks for any help!

• LutzM

  SSC Guru

  Points: 107049

  More actions

  December 1, 2010 at 12:04 pm

  #1257547

  You can simply expand your current CHECK constraint:

  CHECK ((actionId IN(1,2) and associatedId is null) OR (actionId =3 and associatedId is not null))

  As a side note: You should always use a data type for a constant that'll match the column definition. In other words: instead of '2' (representing a character) use 2 (an integer). Even though SQL Server is capable to convert it (by implicit conversion) you should avoid that additional load. It'll also make it obvious that you're referencing a column with integer values so noone would try by mistake to change the CHECK constraint to actionId IN('a',1,2)...

  ---

  
  Lutz
  A pessimist is an optimist with experience.

  How to get fast answers to your question[/url]
  How to post performance related questions[/url]
  Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]