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:

enter image description here enter image description here enter image description here enter image description here

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.

alter table ExampleTable
    add (new_column varchar(20) default 'value1',
         constraint ckExampleTable check (new_column in ('value1', 'value2', 'value3')));
| improve this answer | |
  • 1
    @Phil - Apart from the go looks valid per my reading of the diagram and works fine in Oracle sql fiddle – Martin Smith Dec 4 '12 at 19:23

You should actually do this as two different statements:

    ADD new_column VARCHAR(20) DEFAULT 'value1'

    ADD CONSTRAINT CK_exampleTable_newColumn CHECK (new_column IN ('value1','value2','value3'))
| improve this answer | |
  • As a note, this is SQL Server syntax since I answered the question prior to when it had the Oracle tag and appeared to be SQL Server to me. – cfradenburg Dec 4 '12 at 20:23

SQL Server:

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.