July 25, 2014 at 1:53 pm
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 );
July 25, 2014 at 2:19 pm
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.
July 25, 2014 at 2:20 pm
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. SelburgJuly 25, 2014 at 2:23 pm
Argh, I should have been able to figure that out.
Thanks!
July 26, 2014 at 4:43 am
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
CREATE TABLE CheckConstraintTest
(
CTId INT NOT NULL ,
Goal INT NULL ,
CONSTRAINT CK_CheckConstraintTest_Goal CHECK (
(Goal IS NOT NULL)
OR
(CTId <> 1)
)
);
Tom
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply