-
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
-
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
-
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|