Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Check constraint on multiple columns Expand / Collapse
Author
Message
Posted Friday, July 25, 2014 1:53 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Tuesday, December 23, 2014 12:53 PM
Points: 99, Visits: 765
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 );


Post #1596394
Posted Friday, July 25, 2014 2:19 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, July 25, 2014 3:48 PM
Points: 26, Visits: 223
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.
Post #1596400
Posted Friday, July 25, 2014 2:20 PM This worked for the OP Answer marked as solution


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Thursday, December 11, 2014 5:11 PM
Points: 2,717, Visits: 3,857
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 you'll likely increase the number and quality of responses you get to your question.

Jason L. Selburg
Post #1596401
Posted Friday, July 25, 2014 2:23 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Tuesday, December 23, 2014 12:53 PM
Points: 99, Visits: 765
Argh, I should have been able to figure that out.

Thanks!
Post #1596404
Posted Saturday, July 26, 2014 4:43 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Tuesday, December 23, 2014 7:18 PM
Points: 7,930, Visits: 9,654
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
Post #1596499
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse