default a column
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: default a column

  1. #1
    Join Date
    Apr 2001
    Location
    Berlin
    Posts
    65

    Angry

    When you set a column of a table to use a default value, do you have to make that column not null?

    I defaulted a column of a table to be 'BOY'. The data dictionary shows that the column is defaulted but when I checked from that column, I can still see some nulls values indicating that the default is not being used.

    ALTER TABLE EMPLOYEE MODIFY (EMP_TYPE DEFAULT 'BOY');

    select column_name, data_default, table_name from dba_tab_columns where owner in ('DANIEL') and table_name in ('EMPLOYEE');

    COLUMN_NAME DATA_DEFAULT TABLE_NAME
    ---------------------- -------------------- -------------------------
    EMP_TYPE 'BOY' EMPLOYEE



    SQL> select EMP_TYPE from EMPLOYEE;

    EMP_TYPE
    --------










    MAN

    EMP_TYPE
    --------
    MAN

    12 rows selected.

  2. #2
    Join Date
    Jan 2001
    Posts
    3,131
    I'm pretty sure that if you do not specify not null it will only affect future inserts. Try it out. Insert into that table but leave the col null.

    MH

  3. #3
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    Originally posted by Mr.Hanky
    I'm pretty sure that if you do not specify not null it will only affect future inserts. Try it out. Insert into that table but leave the col null.

    MH
    Not true:
    Code:
    SQL> select * from xyz;
    
             X
    ----------
             1
             2
    
    SQL> alter table xyz add (y number(10) default 0);
    
    Table altered.
    
    SQL> select * from xyz;
    
             X          Y
    ---------- ----------
             1          0
             2          0
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  4. #4
    Join Date
    Apr 2001
    Location
    Berlin
    Posts
    65
    Do you think it has something to do with the way I
    issued the command?

    ALTER TABLE EMPLOYEE MODIFY (EMP_TYPE DEFAULT 'BOY');

    The column is a VARCHAR2(15)

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