Take the 2-minute tour ×
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. It's 100% free, no registration required.

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
add comment

2 Answers

up vote 5 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. –  FreshPhilOfSO 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
add comment

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'))
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
1  
Can you explain the reasoning behind saying 'you should do this in two statements'? –  Paul White Dec 5 '12 at 8:42
add comment

Your Answer

 
discard

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.