Check constraint on multiple columns

Question

Post reply

Check constraint on multiple columns

sqlpadawan_1

SSCommitted

Points: 1571
More actions
July 25, 2014 at 1:53 pm

#310311

I have a case where if the Id field is a specific value, I don't want to allow null in another field, but if the Id value <> a specific value, null is ok.
In the example below, inserting the first record should succeed, the second should succeed, and the 3rd should fail. Right now the 2nd two fail. I gotta be missing something easy, but I can't figure it out.
Any ideas?
USE tempdb
GO
IF OBJECT_ID('tempdb.dbo.CheckConstraintTest') IS NOT NULL
DROP TABLE tempdb.dbo.CheckConstraintTest;
CREATE TABLE CheckConstraintTest
(
CTId INT NOT NULL ,
Goal INT NULL ,
CONSTRAINT CK_CheckConstraintTest_Goal CHECK ( CTId = 1
AND Goal IS NOT NULL )
);
INSERT INTO CheckConstraintTest
( CTId, Goal )
VALUES ( 1, 0 );
INSERT INTO CheckConstraintTest
( CTId, Goal )
VALUES ( 2, NULL );
INSERT INTO CheckConstraintTest
( CTId, Goal )
VALUES ( 1, NULL );

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

gmrose SSC Eights! Points: 988 More actions · Answer 1

The first INSERT succeeds because both "CTId = 1" is TRUE and "Goal IS NOT NULL" is TRUE.

The second INSERT fails because "CTId = 1" is not TRUE.

The third INSERT fails because "Goal IS NOT NULL" is not TRUE.

Since you have "AND" in the test, both conditions must be TRUE for success.

Jason Selburg SSC-Insane Points: 24641 More actions · Answer 2

Wrap your conditions in parenthesis and add an OR

CREATE TABLE CheckConstraintTest

(

CTId INT NOT NULL ,

Goal INT NULL ,

CONSTRAINT CK_CheckConstraintTest_Goal CHECK (

(CTId = 1 AND Goal IS NOT NULL )

OR

(CTId <> 1)

)

);

______________________________________________________________________

Personal Motto: Why push the envelope when you can just open it?

If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.

Jason L. Selburg

sqlpadawan_1 SSCommitted Points: 1571 More actions · Answer 3

Argh, I should have been able to figure that out.

Thanks!

TomThomson SSC Guru Points: 104773 More actions · Answer 4

Jason Selburg (7/25/2014)
Wrap your conditions in parenthesis and add an OR
CREATE TABLE CheckConstraintTest
(
CTId INT NOT NULL ,
Goal INT NULL ,
CONSTRAINT CK_CheckConstraintTest_Goal CHECK (
(CTId = 1 AND Goal IS NOT NULL )
OR
(CTId <> 1)
)
);

That's logicall correct but a bit redundant, you could just use