Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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.

share|improve this question
Which DBMS is this? SQL Server? Please tag the question appropriately. – Mike Walsh Dec 4 '12 at 19:01
up vote 7 down vote accepted
alter table ExampleTable
    add (new_column varchar(20) default 'value1',
         constraint ckExampleTable check (new_column in ('value1', 'value2', 'value3')));
share|improve this answer
It's an Oracle question. – Phil Dec 4 '12 at 19:20
@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'))
share|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
Can you explain the reasoning behind saying 'you should do this in two statements'? – Paul White Dec 5 '12 at 8:42

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

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