Adding a column with a default value to an existing table.
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3

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

  1. #1
    Join Date
    Jan 2000
    Location
    Chester, England.
    Posts
    818

    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?

  2. #2
    Join Date
    Oct 2002
    Posts
    182
    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

  3. #3
    Join Date
    Jan 2000
    Location
    Chester, England.
    Posts
    818
    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
  •  



Click Here to Expand Forum to Full Width