SQL set allowed values for a column

Question

I want to make an ALTER TABLE expression which adds a new column and sets a default value and additionaly defines the allowed values for that column. It's a text column, and allowed should be only 'value1', 'value2' and 'value3'. Default should be 'value1'

According to following syntax diagrams:

I'm getting to this point

ALTER TABLE exampleTable ADD COLUMN new_column VarChar(20) DEFAULT 'value1' 

but I'm absolutely not sure how to set the allowed values.

Is it possible to make somethin like

CONSTRAINT CHECK new_column IN ('value1', 'value2', 'value3)

? I must admit the search condition diagram is quite confusing me.

Answer 1

alter table ExampleTable
    add (new_column varchar(20) default 'value1',
         constraint ckExampleTable check (new_column in ('value1', 'value2', 'value3')));

Answer 2

You should actually do this as two different statements:

ALTER TABLE test
    ADD new_column VARCHAR(20) DEFAULT 'value1'

ALTER TABLE test
    ADD CONSTRAINT CK_exampleTable_newColumn CHECK (new_column IN ('value1','value2','value3'))

Answer 3

SQL Server:

ALTER TABLE table_name
    ADD CONSTRAINT constraint_name CHECK (column_name IN ('aaa', 'bbb', ...))