MYSQL insert null for not null default column

Question

I have curious about why some not null column already set default value, but during insert sql script, it will throw error.

Here is the sample table

drop table if exists `delivery`;
create table `delivery`(
    `price` BIGINT not null default 0,
    `created_time` TIMESTAMP(6) not null default CURRENT_TIMESTAMP (6)
) ENGINE=INNODB DEFAULT CHARSET=UTF8MB4
;

Let's say, execute the three statement below, only the second statement will not throw error

insert into `delivery` (`price`,`created_time`) values (null, null);
insert into `delivery` (`price`,`created_time`) values (1, null);
insert into `delivery` (`price`,`created_time`) values (null, now());

So does it have anyway to insert null for bigint datatype column and make it execute success? And any ideas for the logic behind.

These kind of behaviours are vastly affected by the SQL_MODE variable. — Álvaro González
– Álvaro González, Commented Apr 16, 2020 at 18:02

The Impaler · Accepted Answer · 2020-04-16 17:59:21Z

2

You can't insert null values since you have NOT NULL constraints on the columns.

The first and third statement throw an error since you are trying to insert a null value into the column price and/or created_time, and that clearly doesn't satisfy the constraint(s).

If you really want to allow null values, then remove the NOT NULL constraint on the column(s).

Alternatively, you could sucessfully run your SQL statements as shown below:

insert into `delivery` () values ();
insert into `delivery` (`price`) values (1);
insert into `delivery` (`created_time`) values (now());

select * from `delivery`;

Result:

price  created_time              
-----  --------------------------
    0  2020-04-16 09:48:23.505147
    1  2020-04-16 09:48:25.549202
    0  2020-04-16 09:48:26.0

EDIT:

The second query actually succeeds in MySQL 5.7; it silently ignores the explicit null value and uses the default value.

It seems that the behavior was fixed in MySQL 8.x since it fails now (as it should).

edited Apr 16, 2020 at 17:59

answered Apr 16, 2020 at 17:45

The Impaler

49.3k10 gold badges50 silver badges90 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

kmoser Over a year ago

Why does the 2nd statement work when it sets created_time to null, and created_time is also NOT NULL?

The Impaler Over a year ago

@kmoser The second one fails with: Error: Column 'created_time' cannot be null - SQLState: 23000 - ErrorCode: 1048. See example at db-fiddle.com/f/jSe88MiqpD2D9haN9sgDDw/0

oka96 Over a year ago

@TheImpaler I was able to execute it for the 2nd statement, but it's local setup db

kmoser Over a year ago

@TheImpaler Interesting, I tested the 2nd query on MySQL 5.7 and it worked.

Solarflare Over a year ago

The null-behaviour specifically for timestamps is determined by the explicit_defaults_for_timestamp- and strict-mode-configuration. Check them. Other incredible fun stuff you can try with timestamps and nulls: the behaviour can be different when you insert 1 or 2 rows. For all other datatypes, the behaviour is as The Impaler describes.

Collectives™ on Stack Overflow

MYSQL insert null for not null default column

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related