I have a column that is defined by DEFAULT ON NULL 0 and would like to alter it to DEFAULT 0 NOT NULL. I tried:

1. ALTER TABLE TAB MODIFY (COL NUMBER DEFAULT 0) sets NULLABLE='Y' which I don't want
2. ALTER TABLE TAB MODIFY (COL NUMBER DEFAULT 0 NOT NULL) raises an error that says NOT NULL is already set.

I'm aware I can achieve it by executing both statements, but it doesn't feel right to temporarily release NOT NULL - so is there a single statement that directly releases only DEFAULT ON NULL?

I have a column that is defined by DEFAULT ON NULL 0 and would like to alter it to DEFAULT 0 NOT NULL. I tried:

1. ALTER TABLE TAB MODIFY (COL NUMBER DEFAULT 0) sets NULLABLE='Y' which I don't want
2. ALTER TABLE TAB MODIFY (COL NUMBER DEFAULT 0 NOT NULL) raises an error that says NOT NULL is already set.

I'm aware I can achieve it by executing both statements, but it doesn't feel right to temporarily release NOT NULL - so is there a single statement that directly releases only DEFAULT ON NULL?

• sql
• oracle-database
• default

• Are you really sure about that the second option raises an exception ? – Barbaros Özhan Commented Feb 10 at 15:20
• Unfortunately yes! – NablaDelta Commented Feb 10 at 15:44

1 Answer 1

You can't do this in one step. When you remove DEFAULT it automatically resets nullability, so you have to tell it to make it NOT NULL. As far as I know there is no way to do this in one single command. I believe you should execute both, like you said.