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.


3 Answers 3

alter table ExampleTable
    add (new_column varchar(20) default 'value1',
         constraint ckExampleTable check (new_column in ('value1', 'value2', 'value3')));
  • 1
    @Phil - Apart from the go looks valid per my reading of the diagram and works fine in Oracle sql fiddle Dec 4, 2012 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'))
  • 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. Dec 4, 2012 at 20:23

SQL Server:

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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