Skip to Main Content
  • Questions
  • ora-30667 cannot drop not null constraint on a default on null column

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Pedro.

Asked: December 15, 2015 - 11:05 pm UTC

Last updated: April 24, 2020 - 2:41 am UTC

Version: Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production

Viewed 10K+ times! This question is

You Asked

Hi, before all, appreciate any help and sorry about my eglish

I'm a student and my teacher created a schema to me, so i could work with oracle database. It was working well, but today i deleted some tables and added others and when i try to insert something in the new tables it gives me that error.

I read somethings about that error, and some they say do the purge recyclebin solve, but it didn't work for me, it stills giving me that error

even that simple exmple, it gives me error:
create table example(example_id number(10), example_name varchar(255));
insert into example(example_id, example_name) values(1, 'example');


Please help me, i have to do the work but that error is not letting me do it.

and Connor said...

SQL> create table example(example_id number(10), example_name varchar(255));

Table created.

SQL> insert into example(example_id, example_name) values(1, 'example');

1 row created.


works fine. Your error is due to something else

SQL> create table T  (x int default on null 0 not null );

Table created.

SQL> alter table T modify x null;
alter table T modify x null
*
ERROR at line 1:
ORA-01451: column to be modified to NULL cannot be modified to NULL


You are trying to make a column with a DEFAULT ON NULL clause nullable.

If you want to make the column nullable and keep the default, just do

SQL> alter table T modify x default 0;

Table altered.


Hope this helps.

Rating

  (2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Possibly a bug in 12c

Jeffrey Kemp, December 16, 2015 - 4:45 am UTC

This question was crossposted to StackOverflow here:

http://stackoverflow.com/questions/34300909/ora-30667-cannot-drop-not-null-constraint-on-a-default-on-null-column

I think there's more going on here (the OP is running an insert, not altering the table). Possibly a bug in 12c.
Connor McDonald
December 16, 2015 - 7:15 am UTC

StackOverflow ? What's that ? :-)

Had a look - yeah, *recursive* perhaps the key word the poster omitted here, plus a sea of other information that would have been useful

A reader, April 23, 2020 - 8:32 am UTC

Hi Tom,
I dont want to make the values to 0 to that specified column, instead of 0 what can I use?

Thanks a lot:)
Connor McDonald
April 24, 2020 - 2:41 am UTC

Anything you want...but if you want a column to be not null...then it has to be not null :-)