11

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.

Valentino Ru
  • 213
  • 1
  • 2
  • 6

3 Answers3

17
alter table ExampleTable
    add (new_column varchar(20) default 'value1',
         constraint ckExampleTable check (new_column in ('value1', 'value2', 'value3')));
Vincent Malgrat
  • 4,334
  • 15
  • 20
Keith Tate
  • 409
  • 2
  • 4
  • 1
    @Phil - Apart from the `go` looks valid per my reading of the diagram and works fine in Oracle [sql fiddle](http://www.sqlfiddle.com/#!4/1dd93/1) – Martin Smith Dec 04 '12 at 19:23
7

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'))
cfradenburg
  • 668
  • 3
  • 7
  • 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 04 '12 at 20:23
0

SQL Server:

ALTER TABLE table_name
    ADD CONSTRAINT constraint_name CHECK (column_name IN ('aaa', 'bbb', ...))
Marco
  • 3,640
  • 4
  • 20
  • 29
Bohdan
  • 101
  • 1