Search code examples
mysqlsqlconstraintsalter

Alter Column Check error

I have a column in SQL that I need to add an additional check constraint to. Currently the column is set to,

PropType Varchar(23) Check(PropType In('Potential Property','Vacant Property','Work-In-Progress-Property','Sold Property','Rental Property'))Not Null;

With this alter statement i am trying to change the character limit to 50 as well as add an additional check figure 'Vacant Property,Work-In-Progress-Property',

alter table property
alter column PropType Varchar(50) Check(PropType In('Potential Property','Vacant Property','Work-In-Progress-Property','Vacant Property,Work-In-Progress-Property','Sold Property','Rental Property'))Not Null;

I am getting this error,

Msg 156, Level 15, State 1, Line 2 Incorrect syntax near the keyword 'Check'.

Does anyone have any idea why? this is literally the exact same statement i used to make the table except it uses 50 characters and has an extra check figure. I tried using 'Add Constraint' in front of check and i have no luck.

Solution 
  • alter table property alter column PropType Varchar(50) NOT NULL; 

alter table property add Check(PropType In('Potential Property','Vacant Property','Work-In-Progress-Property','Vacant Property,Work-In-Progress-Property','Sold Property','Rental Property'));