default a column

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.

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

Quote:

---

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

---

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)