December 1, 2010 at 11:07 am
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!
December 1, 2010 at 12:04 pm
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)...
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply