Constraint based on the value of a different field

• steven.bird

  Grasshopper

  Points: 21

  More actions

  December 1, 2010 at 11:23 am

  #231764

  I had incorrectly posted this to the SQL server 2000 forum, but I am currently working with 2008: So...

  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!

• Mark Cowne

  One Orange Chip

  Points: 26983

  More actions

  December 1, 2010 at 11:49 am

  #1257540

  Try this

  CREATE TABLE #temp (

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

  ,actionId int not null

  ,associatedId int null

  ,CHECK ((actionId IN (1,2) AND associatedId IS NULL) OR (actionId=3 AND associatedId IS NOT NULL))

  )

  ____________________________________________________

  Deja View - The strange feeling that somewhere, sometime you've optimised this query before

  How to get the best help on a forum

  http://www.sqlservercentral.com/articles/Best+Practices/61537