CODEWITHSUNDEEP
mysqldefault-value
Shahid Ghafoor
Shahid Ghafoor

Reputation: 3103

MySQL default value on NULL

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?

Upvotes: 12

Views: 23363

Answers (3)

kiran Biradar
kiran Biradar

Reputation: 12742

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));

Upvotes: 1

Bhargav Chudasama
Bhargav Chudasama

Reputation: 7171

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);

Upvotes: 3

Vatev
Vatev

Reputation: 7590

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);

Upvotes: 2

Related Questions