MySQL default value on NULL

Question

I have the following table:

CREATE TABLE `Foo` (
  `id`         int NOT NULL,
  `FirstName`  varchar(255) NULL,
  `LastName`   varchar(255) NOT NULL DEFAULT 'NONE',
  PRIMARY KEY (`id`)
);

When I run the following query it take the default value of 'NONE':

INSERT INTO Foo (`FirstName`) VALUES('FOO');

When I run the following query:

INSERT INTO Foo (`FirstName`, `LastName`) VALUES('FOO', NULL);

it gives an error:

[Err] 1048 - Column 'LastName' cannot be null

What I want to achieve is that if a value is NULL then MySQL should use the DEFAULT value.
Does anybody know the solution?

you define your table saying that the field cannot be null. The default value works on empty, not on null. So, if you don't pass a value for the field it will default to 'NONE'. If you pass any Mysql will check the value (and fail 'cause you said not null) — user2399035
– user2399035, Commented Jun 7, 2018 at 7:37
@LelioFaieta ok .. i know that ... but tell me the solution that if value is null then , db take default value .. I don't want to remove null check — Shahid Ghafoor
– Shahid Ghafoor, Commented Jun 7, 2018 at 7:43

Bhargav Chudasama · Accepted Answer · 2018-06-07 07:45:00Z

3

try this

use NULL instead of NOT NULL

CREATE TABLE `Foo` (
`id`  int NOT NULL ,
`FirstName`  varchar(255) NULL ,
`LastName`  varchar(255) NULL DEFAULT  'NONE' , 
PRIMARY KEY (`id`)
);

and query like this

use DEFAULT instead of NULL

INSERT INTO Foo ( `FirstName`, `LastName` ) VALUES('FOO', DEFAULT);

edited Jun 7, 2018 at 7:45

answered Jun 7, 2018 at 7:33

Bhargav Chudasama

7,2256 gold badges24 silver badges41 bronze badges

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

Comments

Vatev · Accepted Answer · 2018-06-07 07:40:27Z

2

When you use NULL it means that you want to set NULL as a value, which can't happen for the NOT NULL column.

You can explicitly insert the default value by using DEFAULT:

INSERT INTO Foo ( `FirstName`, `LastName` ) VALUES('FOO', DEFAULT);

answered Jun 7, 2018 at 7:40

Vatev

7,5801 gold badge36 silver badges42 bronze badges

2 Comments

Shahid Ghafoor Over a year ago

but at run time, its not possible to check which value is null or not .. so if db receive null, it should take default value

user2399035 Over a year ago

you have to check on the application side if value is null and in case pass DEFAULT

kiran Biradar · Accepted Answer · 2020-10-22 07:06:57Z

2

Try removing NOT NULL constraint.

INSERT INTO Foo ( `FirstName`) VALUES('FOO');

When you are not inserting any value manually to lastname you are literally inserting 'none'(default value) into your table hence NOT NULL constraint passes.

INSERT INTO Foo ( `FirstName`, `LastName` ) VALUES('FOO', NULL);

Here you are inserting NULL manually to lastname hence you are literally inserting NULL into your table hence NOT NULL constraint fails.

Solution:

use COALESCE() function while inserting into table, Which will return non null parameter.

INSERT INTO Foo ( FirstName, LastName ) VALUES('FOO', COALESCE(@value, default));

edited Oct 22, 2020 at 7:06

answered Jun 7, 2018 at 7:36

kiran Biradar

12.8k3 gold badges21 silver badges44 bronze badges

2 Comments

Shahid Ghafoor Over a year ago

I want when value is null then it should take default value, that's I want to achieve

kiran Biradar Over a year ago

Use COALESCE() function while inserting into table, Which will return non null parameter. NSERT INTO Foo ( FirstName, LastName ) VALUES('FOO', COALESCE(@value, default));

Collectives™ on Stack Overflow

MySQL default value on NULL

3 Answers 3

Comments

2 Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

2 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related