Constraint Syntax Query

[Noonoo]

Hello.

I'm trying to create a constraint on a table which prevents null values in colummn B when the column A bit field is '1'. It seems that a contraint is the ideal way and I've tried the following.

Alter Table TableName
Add Contraint CN_ContraintName
Check
(B IS NOT NULL where A = 1)

However, I get an error message advising there's a syntax error near the keyword where.

I also tried an If clause but got a similar error message.

Does anyone know how to do this?

Ideally I'd also like to contrain B to being an integer when A is 1.

Thanks

 

[JamesLean]

This script should illustrate how to achieve what you want:

CREATE TABLE #con (colA bit, colB varchar(10))

ALTER TABLE #con
ADD CONSTRAINT cn_check
CHECK ((colA = 1 AND ISNUMERIC(colB) = 1) OR (colA = 0))

INSERT #con VALUES (0, NULL)
INSERT #con VALUES (1, NULL)
INSERT #con VALUES (0, 'james')
INSERT #con VALUES (1, 'james')
INSERT #con VALUES (1, 45)

SELECT * FROM #con

DROP TABLE #con

--James

 

[Noonoo]

Thanks James. Took a little bit of fiddling but it's working a treat