CODEWITHSUNDEEP
sqlsql-servert-sqlconstraints
LongLouis
LongLouis

Reputation: 41

CASE Statement inside CHECK Constraint Error: An expression of non-boolean type specified in a context where a condition is expected, near ')'

I am trying to use a CASE statement inside a CHECK constraint in SQL Server, but I get this error:

Msg 4145, Level 15, State 1, Line 15
An expression of non-boolean type specified in a context where a condition is expected, near ')'.

create table dbo.Advertisement
(
    advertisement_id int NOT NULL PRIMARY KEY,
    roadside_bottom varchar (20),
    roadside_top_bottom varchar (20),
    curbside varchar (20),
    rearside varchar (20),
    headliner varchar(10),
    bus varchar (10),
    CONSTRAINT bottom_pass 
        CHECK(CASE 
                  WHEN roadside_bottom = 'King' AND roadside_top_bottom IS NULL THEN 1 
                  WHEN roadside_bottom = 'Super King' and roadside_top_bottom IS NULL THEN 1 
              END)  
);

I need help with the syntax for.

Upvotes: 1

Views: 716

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270351

You can use case, but it returns a value and that needs to be compares to something:

CONSTRAINT bottom_pass CHECK
(
    CASE WHEN roadside_bottom = 'King' AND roadside_top_bottom IS NULL THEN 1 
         WHEN roadside_bottom = 'Super King' and roadside_top_bottom IS NULL THEN 1
    END = 1
)

However, this would more concisely be written as:

CONSTRAINT bottom_pass CHECK
(
    roadside_top_bottom IS NULL AND
    roadside_bottom IN ('King', 'Super King')
)

Upvotes: 2

Related Questions