Skip to Main Content
  • Questions

  • Cannot insert null values to a column defined not null with default constraint

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Faizal.

Asked: May 16, 2017 - 6:04 am UTC

Last updated: December 12, 2018 - 3:38 am UTC

Version: Oracle database 11g

Viewed 10K+ times! This question is

You Asked

Hi Tom,

I'm trying to insert null values to a column defined not null with default constraint .
But getting error ORA=01400 Cannot insert null values.

Below is the table I created .

create table tmp_test(
column1 varchar2(10) default 1 not null,
column2 number(10) default 2 not null);

Thanks,
Faizal

and Connor said...

A default is "if you do NOT explicitly give us a value, use this one".

If you explicitly state null, then we will respect that instruction.

SQL> create table tmp_test(
  2  column1 varchar2(10) default 1 not null,
  3  column2 number(10) default 2 not null);

Table created.

SQL>
SQL> insert into tmp_test values (10,10);

1 row created.

SQL> insert into tmp_test (column1) values (20);

1 row created.

SQL> insert into tmp_test values (null,null);
insert into tmp_test values (null,null)
                             *
ERROR at line 1:
ORA-01400: cannot insert NULL into ("MCDONAC"."TMP_TEST"."COLUMN1")


SQL>
SQL> select * from tmp_test;

COLUMN1       COLUMN2
---------- ----------
10                 10
20                  2

2 rows selected.


In 12c, we have a "default on null" extension which will behave in the way that you want.

Rating

  (4 ratings)

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

Comments

Default NULL not null

Rj, August 02, 2018 - 11:53 pm UTC

will it allow to insert,update null value
if we give dafault value as NULL for a column which has Not Null constraint on it ?

Thanks @02-Aug-2018
Chris Saxon
August 06, 2018 - 2:27 pm UTC

If you have a not null constraint, you have to provide a non-null value on insert/update:

create table t (
  c1 int default null not null
);

insert into t values ( null );

ORA-01400: cannot insert NULL into ("CHRIS"."T"."C1")

insert into t values ( 1 );

1 row inserted.

update t set c1 = null;

ORA-01407: cannot update ("CHRIS"."T"."C1") to NULL

Chuck Jolley, August 06, 2018 - 3:12 pm UTC

You also can't sneak it in this way:
create table t2(a int, b int default null not null);

Table created.

insert into t2(a) values(1);
insert into t2(a) values(1)
*
ERROR at line 1:
ORA-01400: cannot insert NULL into ("CHAJOL"."T2"."B")


Chris Saxon
August 06, 2018 - 4:27 pm UTC

Yep, good point.

one more add on question

prasanth, December 05, 2018 - 4:40 am UTC

i am not sure if an adon question related to this question is permited. if not parden me.

when i used a similar setup, i was trying for better option with the next version of default but as below

column is “Not Nullable” and “default on Null 0 “

now the insert is working, even when the input is null to 0

but update statement failed with null value cannot be inserted error?
Connor McDonald
December 12, 2018 - 3:38 am UTC

That is correct. "default" is for new rows not updated rows.

But I don't see that as great issue. Because you would:

a) insert a row (which will pick up the default value if some passed null)
b) if someone updates the row, they can either:

- not specify the column (it will stay at the default value)
- specify a new value (and we update it to that)
- specify null (and we should error out because the column would be not null)

A reader, August 28, 2019 - 11:30 am UTC


more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.