Adding a column with a default value to an existing table.

Having a bit of trouble with what looks like a simple task ...

Table is called FRED And when I try and add the column I get an error. I can create an identical table using a CREATE TABLE ... AS SELECT * FROM ... and I can add the column with no problem.

e.g.

SQL> create table johnny as (select * from fred);

Table created.

SQL> ALTER TABLE johnny
2 ADD (VERIFICATION_1 VARCHAR2(1) DEFAULT 'N' NOT NULL);

Table altered.

SQL> desc johnny;
Name Null? Type
--------------------------TNUC_ID NOT NULL NUMBER(10)
TRL_ID NOT NULL VARCHAR2(10)
ITEM_NO NOT NULL VARCHAR2(20)
CREATED_BY NOT NULL VARCHAR2(30)
CREATE_DATE NOT NULL DATE
BATCH_NO VARCHAR2(50)
TRLG_CODE VARCHAR2(10)
QTY_REQUESTED FLOAT(40)
UPDATED_BY VARCHAR2(30)
UPDATE_DATE DATE
VERIFICATION_1 NOT NULL VARCHAR2(1)

SQL> desc fred
Name Null? Type
----------------------------------------------------------------- -------- -----------------
TNUC_ID NOT NULL NUMBER(10)
TRL_ID NOT NULL VARCHAR2(10)
ITEM_NO NOT NULL VARCHAR2(20)
CREATED_BY NOT NULL VARCHAR2(30)
CREATE_DATE NOT NULL DATE
BATCH_NO VARCHAR2(50)
TRLG_CODE VARCHAR2(10)
QTY_REQUESTED FLOAT(40)
UPDATED_BY VARCHAR2(30)
UPDATE_DATE DATE

SQL> alter table fred
2 ADD (VERIFICATION_1 VARCHAR2(1) DEFAULT 'N');
alter table trial_nucs
*
ERROR at line 1:
ORA-01400: cannot insert NULL into ()

Can't find any complicated constraints on this table ... so whats going on?

Work around.
Add the column.

alter table fred
ADD VERIFICATION_1 VARCHAR2(1);

Set all your values.
update fred
set VERIFICATION_1 = 'N';

Then:
alter table fred modify verification_1 default 'N';

Actually played around and did following, which worked fine without updating the table with 'N' values.

ALTER TABLE xxxxx
ADD col_name VARCHAR2(1);

ALTER TABLE xxxxx
ADD constraint con_name_chk CHECK (xxxxx IN ('Y','N'));

Which gives me the advantage of getting to name the constraint.

The default value it seems was not a business rule, but a developer's idea.