I have two columns in an Oracle SQL database. The two columns have a data_default of '0000'

on sqldeveloper they look like the below:

I would like to remove the data_default (don't ask why I want to do that in nullable = No columns) and reach the following state:

I have tried the following query:

ALTER TABLE table_name MODIFY column_name DEFAULT NULL

But the result I get looks like this:

So two questions:

1. What is the difference between (null) and NULL if any?
2. Is there a way to reach the required state?

CodePudding user response：

The (null) in your first two images is SQL Developer showing a dummy value for a true null, so you have something to see. If you go to Tools->Preferences->Database->Advanced there is an option for 'Display Null Value As', which will be set to "(null)". If you change that to something else then that will be show in your quesry output instead.

In the third image the data_default value is not really null, it is the (long) string value 'NULL'.

If you have never set a default for a column then the data_default is really null, but if you set it and then try to unset it then it doesn't revert to a true null value, it is left as whatever value you supply for the modify, as you suppllied it - including the case. Your set it to NULL so it's shown as NULL; if you modified it to null then it would show null.

It doesn't matter here. Essentially what happens is that when you perform an insert and omit that column, the value form the data_default is embedded in the statement. What it actually does under the hood is probably something rather more complicated, but you can think of it like dynamic SQL, with it converting:

'insert into table_name (id) values (42)'

to

'insert into table_name (id, column_name) values (42, '
  || (select data_default from user_tab_columns where column_name = 'COLUMN_NAME')
  || ')'

so it doesn't matter if that ends up as

'insert into table_name (id, column_name) values (42, null)'

or

'insert into table_name (id, column_name) values (42, NULL)'

or

'insert into table_name (id, column_name) values (42, NuLl)'

And before you changed it back to null, it would have been converted to, effectively:

'insert into table_name (id, column_name) values (42, ''0000'')'

You can see that happening if you create a table with a column with no default, then set a default, then set it to null:

create table table_name (id number, column_name varchar2(4));

select column_name, data_type, data_default from user_tab_columns;

insert into table_name (id) values (1);

alter table table_name modify column_name default '0000';

select column_name, data_type, data_default from user_tab_columns;

insert into table_name (id) values (2);

alter table table_name modify column_name default NuLl;

select column_name, data_type, data_default from user_tab_columns;

insert into table_name (id) values (3);

select * from table_name;

fiddle

The actual defaulted values are true nulls (shown in italics in db<>fiddle, not as "(null)") when there was no default and where there was a null default.

So, there is no practical difference between it showing "(null)" in your second image and "NULL" in the third. It's just an artefact of the value being set and reverted, and how Oracle handled and records that. it doesn't affect the data you insert into your table later.