CODEWITHSUNDEEP
sql-serverconstraints
Whitewolf
Whitewolf

Reputation: 186

Check Constraint with Case When

Trying to add a check constraint to the following table:

CREATE TABLE TEST_A 
(
     NAME VARCHAR(55), 
     Country VARCHAR(50)
)

ALTER TABLE TEST_A 
  ADD CONSTRAINT CK_GBR_TO_IND
      CHECK (Country = CASE WHEN 'GBR' THEN 'IND' ELSE COUNTRY END);

I am getting the following error:

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

Upvotes: 0

Views: 775

Answers (2)

TheGameiswar
TheGameiswar

Reputation: 28940

Try a Trigger instead..You Can't use Check Constraint to change values...

create trigger trg_test
on yourtable
instead of insert 
as
Begin

insert into yourtable--assuming it has only country column
select case when country='GBR' then 'IND'
else country end
from Inserted 

end

Upvotes: 2

Backs
Backs

Reputation: 24913

ALTER TABLE TEST_A ADD CONSTRAINT CK_GBR_TO_IND
CHECK (Country IN('GBR', 'IND'));

Upvotes: 1

Related Questions