| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: How to get rid of a column default value ?
Setting the default to NULL changes to behavior to be the same as if a
default value had not been set. However, in dba_tab_columns, the
default_data column would indicate null. If the default value was never set,
the column would be empty (null in type, not name).
The example below demonstrates the behavior:
create table test
(col1 char(4),
col2 char(4) default 'AAAA');
insert into test values (NULL, NULL);
insert into test values ('1', NULL);
insert into test values ('2', 'BBBB');
select * from test;
COL1 COL2
---- ----
1
2 BBBB
insert into test (col1) values ('3');
select col1, length(col1), col2, length(col2)
from test;
COL1 LENGTH(COL1) COL2 LENGTH(COL2)
---- ------------ ---- ------------
1 4 2 4 BBBB 4 3 4 AAAA 4
alter table test
modify col2 default NULL;
insert into test (col1) values ('4');
select col1, length(col1), col2, length(col2) from test;
COL1 LENGTH(COL1) COL2 LENGTH(COL2)
---- ------------ ---- ------------
1 4 2 4 BBBB 4 3 4 AAAA 4 4 4
select table_name, column_name, data_default
from user_tab_columns
where table_name = 'TEST';
TABLE_NAME COLUMN_NAME DATA_DEFAULT
---------- ----------- ------------
TEST COL1 TEST COL2 null
SQL>
SQL> alter table test
2 modify col2 default 'NULL';
SQL> insert into test (col1) values ('5');
SQL> select col1, length(col1), col2, length(col2)
2 from test;
COL1 LENGTH(COL1) COL2 LENGTH(COL2)
---- ------------ ---- ------------
1 4 2 4 BBBB 4 3 4 AAAA 4 4 4 5 4 NULL 4
select table_name, column_name, data_default
from user_tab_columns
where table_name = 'TEST';
TABLE_NAME COLUMN_NAME DATA_DEFAULT
---------- ----------- ------------
TEST COL1 TEST COL2 'NULL'
-----Original Message-----
Sent: Friday, October 04, 2002 2:38 PM
To: Multiple recipients of list ORACLE-L
Louis,
I still have not convinced myself that we have the proper answer.
If you query the DBA_TAB_COLUMNS table DATA_DEFAULT column you will see
that the original NON default created column has a null or blank as the
value for the data_default column. If the default is set to NULL then
the word NULL appears as the data_default value for the column. What if
the column was a char(4) column then it would default to the value"NULL"
which is not the same as blank or nothing.
Still digging for an answer.
Ron
ROR mô¿ôm
>>> Louis_Brouillette_at_UQTR.CA 10/04/02 03:21PM >>>
Thanks Dale, Michael and Ron.
The "default null" is what I thought about first and it almost does the
job. It's just that it appears like there is a default value which is
null. If I don't include the DEFAULT clause, it does nothing. I think
I'll have to live with the "default null".
At 08:29 2002-10-04 -0800, you wrote:
>Louis,
> I believe it is the ALTER TABLE command.
>ALTER TABLE name
>MODIFY ( column datatype);
>match the column name and the datatype but do not include the DEFAULT
>clause .
>Ron
>ROR mª¿ªm
>
> >>> Louis_Brouillette_at_UQTR.CA 10/04/02 11:28AM >>>
>Anyone knows how to get rid of a column default value ? I rtfm and
>search
>metalink with no luck.
Louis Brouillette
Analyste en informatique (DBA)
Universite du Quebec a Trois-Rivieres
Tel: (819) 376-5011 ext. 2435
Email: brouille_at_uqtr.ca
-- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Louis BROUILLETTE INET: Louis_Brouillette_at_UQTR.CA Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ron Rogers INET: RROGERS_at_galottery.org Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Fink, Dan INET: Dan.Fink_at_mdx.com Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).Received on Fri Oct 04 2002 - 16:08:30 CDT
 |
 |