-
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';
- Cookies
-
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.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|