Make column default to NULL explicitly

Question

How do I make a column default to NULL explicitly?

I would like to declare a column in Oracle SQL Developer to be NULL on default. I'm aware of the fact, that NULL will be the default value, if I do not define any default value at all. But how do I define NULL as default, if I would want to do it explicitly?

-- 1: Does not work.
ALTER TABLE MY_TABLE ADD (
  MY_COLUMN TIMESTAMP(6) DEFAULT null
);

-- 2: Does not work.
ALTER TABLE MY_TABLE ADD (
  MY_COLUMN TIMESTAMP(6) DEFAULT NULL
);

-- 3: Does not work.
ALTER TABLE MY_TABLE ADD (
  MY_COLUMN TIMESTAMP(6) DEFAULT (null)
);

-- 4: This works.
ALTER TABLE MY_TABLE ADD (
  MY_COLUMN TIMESTAMP(6)
);

In case 1-3 the default value will be a String ("NULL", "null" or "(null)"), but not an actual NULL value. So, what am I missing here?

// Edit:

Case (a) and (b) correspond to case 1 and 2. A text value of null or NULL is displayed in SQL Developer. Case (c) corresponds to case 4, where a real (null) value is set explicitly. The screenshots were taken on a table's Columns tab in SQL Developer.

SQL Developer http://s1.postimg.org/fclraa0dp/SQL_Developer.png

Solution

As null, NULL and (null) are the same thing, I don't understand what the problem is.

It is also not a SQL Developer "problem".

Oracle simply stores the default expression exactly as you wrote it in the system catalog. SQL Developer simply displays that.

Assume the following statements:

create table my_table (id integer);
alter table my_table add my_column_1 timestamp(6) default null;
alter table my_table add my_column_2 timestamp(6) default null;
alter table my_table add my_column_3 timestamp(6) default (null);

Then

select column_id, column_name, data_type, data_default
from user_tab_columns
where table_name = 'MY_TABLE'
order by column_id;

Will return the following:

COLUMN_ID | COLUMN_NAME | DATA_TYPE    | DATA_DEFAULT
----------+-------------+--------------+-------------
        1 | ID          | NUMBER       |             
        2 | MY_COLUMN_1 | TIMESTAMP(6) | NULL        
        3 | MY_COLUMN_2 | TIMESTAMP(6) | null        
        4 | MY_COLUMN_3 | TIMESTAMP(6) | (null)      

When you extract the DDL from the system, you again get exactly why you have written:

select dbms_metadata.get_ddl('TABLE', 'MY_TABLE', user)
from dual;

returns:

  CREATE TABLE "TK_HIRAC"."MY_TABLE" 
   (    "ID" NUMBER(*,0), 
    "MY_COLUMN_1" TIMESTAMP (6) DEFAULT NULL, 
    "MY_COLUMN_2" TIMESTAMP (6) DEFAULT null, 
    "MY_COLUMN_3" TIMESTAMP (6) DEFAULT (null)
   ) SEGMENT CREATION DEFERRED 
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 
 NOCOMPRESS NOLOGGING
  TABLESPACE "USERS" 

Comments

[Admin]

This still makes the default NULL implicitly. The NULL here specifies that the column is nullable, not that the default value is NULL.

[Admin]

With your edit, that is one of the options the OP gives as something that does not work.

[Admin]

That's good, then. :) It probably means the OP is using some other version though. I'd normally request more specific info from the OP on the question in that case, but that's already been done.

[user1438038]

This will put a default value of NULL, but its simply text. Not (null) in the sense of no value. I'm on SQL Developer 4.0.1.14. What version are you using?

[David Aldridge]

@user1438038 If you're typing NULL into a cell on SQL Developer that sets the default value for the column, then this is not the SQL that is being issued -- SQL Developer is setting DEFAULT "NULL", not SQL.

[JustAMartin]

This behavior is indeed confusing. It leaves impression that the value was stored as a string literal null NULL or (null), even when it's actually legit NULL. I was scratching my head very long when I stumbled upon this issue thinking that I'm doing something wrong.