Search code examples
sqloracle-databasedefault

How to remove DEFAULT ON NULL but keep NOT NULL in a single statement?

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?

Solution

  • 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.