CODEWITHSUNDEEP
sqloracle-databasenulloracle-sqldeveloperdefault-value
user1438038
user1438038

Reputation: 6059

Make column default to NULL explicitly

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

Upvotes: 6

Views: 31089

Answers (2)

user330315
user330315

Reputation:

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"

Upvotes: 7

Husqvik
Husqvik

Reputation: 5809

Try this:

HUSQVIK@panel_management> CREATE TABLE MY_TABLE (C1 NUMBER NULL);

Table created.

HUSQVIK@panel_management> ALTER TABLE MY_TABLE ADD (
  2    MY_COLUMN TIMESTAMP(6) DEFAULT NULL
  3  );

Table altered.

HUSQVIK@panel_management>

works for me.

Upvotes: -1

Related Questions