Check Constraint and Case Statement

Question

Post reply

Check Constraint and Case Statement

temitaio

SSC Veteran

Points: 297
More actions
August 15, 2011 at 9:32 am

#242145

I need help with this check constraint, I get the following error message: "Msg 102, Level 15, State 1, Line 2
Incorrect syntax near '='."
ALTER TABLE <TableName> ADD CONSTRAINT <NameOfConstraint>
CHECK (CASE WHEN <Column1> = 1 THEN <Column2> = 0
AND( <Column3> = 0
OR <Column4> = 0
OR <Column5> = 0
OR <Column6> = 0
OR <Column7> = 0));
Please help!

Viewing 11 posts - 1 through 10 (of 10 total)

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

Kurt W. Zimmerman SSCrazy Eights Points: 8676 More actions · Answer 1

Why are you using a constraint construct here instead of a trigger?

Kurt

Kurt W. Zimmerman
SR DBA
Lefrak Organization
New York, NY

http://www.linkedin.com/in/kurtwzimmerman

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 2

Your constraint seems very odd but if you want help you should include your DDL.

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Michael Valentine Jones SSC Guru Points: 64818 More actions · Answer 3

temitaio (8/15/2011)
I need help with this check constraint, I get the following error message: "Msg 102, Level 15, State 1, Line 2
Incorrect syntax near '='."
ALTER TABLE <TableName> ADD CONSTRAINT <NameOfConstraint>
CHECK (CASE WHEN <Column1> = 1 THEN <Column2> = 0
AND( <Column3> = 0
OR <Column4> = 0
OR <Column5> = 0
OR <Column6> = 0
OR <Column7> = 0));
Please help!

You cannot use a CHECK CONSTRAINT to set the value of of a column.

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 4

Yes a check constraint is used for validation of columns in one table (To check the values).

If you want to change the value of a column then you would need a trigger.

The following is a good article on the use of check constraints.

http://www.craigmullins.com/sql_1298.htm

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

temitaio SSC Veteran Points: 297 More actions · Answer 5

Thanks everyone. Sorry for the lack of clarity

Here it is again

***********************************************************************************

If InformationRestricted is True, InformationNotRestricted cannot be true and InformationRestrictedFromLevel1, InformationRestrictedFromLevel2, InformationRestrictedFromLevel3, InformationRestrictedFromLevel4, InformationRestrictedFromLevel5 cannot be true

Here is the script:

CREATE TABLE EmployeeData

(FirstName varchar(50),

Last Name varchar(50),

Age int,

Address varchar(100),

InformationRestricted bit,

InformationNotRestricted bit,

InformationRestrictedFromLevel1 bit,

InformationRestrictedFromLevel2 bit

InformationRestrictedFromLevel3 bit

InformationRestrictedFromLevel4 bit

InformationRestrictedFromLevel5 bit);

ALTER TABLE EmployeeData ADD CONSTRAINT ck_EmployeeData

CHECK (CASE WHEN InformationRestricted = 1 THEN InformationNotRestricted = 0

AND( InformationRestrictedFromLevel1 = 0

OR InformationRestrictedFromLevel2 = 0

OR InformationRestrictedFromLevel3 = 0

OR InformationRestrictedFromLevel4 = 0

OR InformationRestrictedFromLevel5 = 0));

I need help with this check constraint, I get the following error message: "Msg 102, Level 15, State 1, Line 14

Incorrect syntax near '='."

Or maybe the question I should ask is if this is possible using a check constraint

Thanks again

Lowell SSC Guru Points: 323518 More actions · Answer 6

much easier to comprehend...that whole pseudo code to abstract the logic actually made it harder to understand.

the rules you pasted are actually multiple separate and distinct check constraints....all of them based on whether one of your columns is true/1 or not.

it looks a lot like homework, so that's all the advice i'll give for now, but you need to reread it a little closer to see how 5 or 6 constraints are required .

this would be an example of part of your check constraint though:

CHECK (ColumnName = CASE WHEN MyStatus=1 THEN 0 ELSE 1 END)

others pointed out that it seems much more likely that a trigger is needed, because you seem to want to ASSIGN values, rather than validate the value is in the right set of values. calculated columns might be a possiblility, or just a view to generate the needed values based on that one column

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

temitaio SSC Veteran Points: 297 More actions · Answer 7

I am not trying to assign values to the columns, just trying to ensure the values of the columns = 0 (i.e. false) if InformationRestricted is True

Michael Valentine Jones SSC Guru Points: 64818 More actions · Answer 8

temitaio (8/16/2011)
I am not trying to assign values to the columns, just trying to ensure the values of the columns = 0 (i.e. false) if InformationRestricted is True

Your check constraint is not in the form of a logical condition, and your case expression is invalid

ALTER TABLE <TableName> ADD CONSTRAINT <NameOfConstraint>

CHECK (CASE WHEN <Column1> = 1 THEN <Column2> = 0

AND( <Column3> = 0

OR <Column4> = 0

OR <Column5> = 0

OR <Column6> = 0

OR <Column7> = 0));

You need something like this:

ALTER TABLE MyTable

ADD CONSTRAINT Chk_MyTable

CHECK (

1 =

CASE

WHEN InformationRestricted = 1 and

(1 in (

InformationNotRestricted,

InformationRestrictedFromLevel1,

InformationRestrictedFromLevel2,

InformationRestrictedFromLevel3,

InformationRestrictedFromLevel4,

InformationRestrictedFromLevel5

))

then 0

else 1

end

);

Lowell SSC Guru Points: 323518 More actions · Answer 9

when i read this requirement:

If InformationRestricted is True,
InformationNotRestricted cannot be true
and InformationRestrictedFromLevel1,
InformationRestrictedFromLevel2,
InformationRestrictedFromLevel3,
InformationRestrictedFromLevel4,
InformationRestrictedFromLevel5 cannot be true

i see that as saying a suite of constraints like this:

ALTER TABLE MyTable

ADD CONSTRAINT Chk_MyTableR CHECK ( InformationNotRestricted = CASE WHEN InformationRestricted =1 THEN 0 ELSE 1 END),

CONSTRAINT Chk_MyTable1 CHECK ( InformationRestrictedFromLevel1 = CASE WHEN InformationRestricted =1 THEN 0 ELSE 1 END),

CONSTRAINT Chk_MyTable2 CHECK ( InformationRestrictedFromLevel2 = CASE WHEN InformationRestricted =1 THEN 0 ELSE 1 END),

CONSTRAINT Chk_MyTable3 CHECK ( InformationRestrictedFromLevel3 = CASE WHEN InformationRestricted =1 THEN 0 ELSE 1 END),

CONSTRAINT Chk_MyTable4 CHECK ( InformationRestrictedFromLevel4 = CASE WHEN InformationRestricted =1 THEN 0 ELSE 1 END),

CONSTRAINT Chk_MyTable5 CHECK ( InformationRestrictedFromLevel5 = CASE WHEN InformationRestricted =1 THEN 0 ELSE 1 END)

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 10

Looks like a homework assignment to me and not a very good one at that.

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/