SQL set allowed values for a column

alter-tableconstraintdefault valueoracle

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

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.

Best Answer

alter table ExampleTable
    add (new_column varchar(20) default 'value1',
         constraint ckExampleTable check (new_column in ('value1', 'value2', 'value3')));

Related Solutions

Postgresql – check key if exists in other table without fk constraint

I do agree with Aaron, that not creating a PK (or unique constraint) is a very strange requirement

But you could do something like this:

insert into b (id_b, id_a)
select 1, 2 
where exists (select 1 from a where id_a = 2);

or alternatively:

insert into b (id_b, id_a)
select 1, id_a 
from a where id_a = 2

If you want to insert multiple rows, you can use a values clause:

insert into b (id_b, id_a)
select * 
from (
   values 
      (1,2), (2,3), (3,4)
) as t(id_b, id_a) 
where exists (select 1  
              from a 
              where a.id_a = t.id_a);

This will only make sure that this specific insert will insert correct data. It will not prevent others that don't follow this pattern to insert invalid data. And it is not safe in a multi-user environment where different transactions run the inserts concurrently.

Adding a default constraint in Oracle while altering a column

A default value is not a constraint in Oracle. You simply alter the column as:

SQL> create table tab1 (col1 number, col2 number);

Table created.

SQL> alter table tab1 modify (col2 default 1);

Table altered.

SQL> select * from user_constraints;

no rows selected

SQL> insert into tab1 (col1) values (0);

1 row created.

SQL> select * from tab1;

      COL1       COL2
---------- ----------
         0          1

Best Answer

Related Solutions

Postgresql – check key if exists in other table without fk constraint

Adding a default constraint in Oracle while altering a column

Related Question