MySQL default value on NULL

mysql> insert default value if null
mysql default value if null
mysql default null
mysql date default value
mysql not null default
mysql no default value
mysql string default value
set default value in sql select query

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?


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

MySQL: Select a default value for NULL fields, try this. use NULL instead of NOT NULL CREATE TABLE `Foo` ( `id` int NOT NULL , `FirstName` varchar(255) NULL , `LastName` varchar(255)  Use DEFAULT keyword in MySQL to set default value to NULL. Let us first create a − mysql> create table DemoTable1440 -> ( -> StudentId int NOT NULL AUTO_INCREMENT PRIMARY KEY, -> StudentName varchar(20) DEFAULT NULL, -> StudentAge int DEFAULT NULL -> ); Query OK, 0 rows affected (0.55 sec)


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

MySQL default value on NULL, Abstract: It is common practice in MySQL table design that fields are declared as NOT NULL but some non-sense DEFAULT values are  Primary keys cannot contain NULL values, in accordance with proper database design and with how MySQL functions. If an ENUM column is set as NOT NULL, the default value will automatically be the first of the possible allowed values.


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.

Using NULL as default values, Use DEFAULT keyword in MySQL to set default value to NULL. Let us first create a −mysql> create table DemoTable1440 -> ( -> StudentId int  For most of the data types, MySQL provides ‘NULL’ as a DEFAULT value. In the case of Primary Key columns, these cannot contain ‘NULL’ values. In this case, a DEFAULT unique value is a good way to handle this. For predefined standards or business rules, we can use DEFAULT values instead of manually specifying the field every time.


How to set default value to NULL in MySQL?, Note the column Summary that is marked not null but has a default value of an empty string. Now, try to insert a null value into this column. If a data type specification includes no explicit DEFAULT value, MySQL determines the default value as follows: If the column can take NULL as a value, the column is defined with an explicit DEFAULT NULL clause. If the column cannot take NULL as a value, MySQL defines the column with no explicit DEFAULT clause.


MySQL Quirk with Not Null Columns and Default Values, Two more options are to dictate whether or not the value of a column can be NULL and to set a default value. The NULL value, in databases and  If a data type specification includes no explicit DEFAULT value, MySQL determines the default value as follows: If the column can take NULL as a value, the column is defined with an explicit DEFAULT NULL clause. If the column cannot take NULL as a value, MySQL defines the column with no explicit DEFAULT clause.


NULL and Default Values | MySQL Database Design, For most of the data types, MySQL provides 'NULL' as a DEFAULT value. In the case of Primary Key columns, these cannot contain 'NULL' values. In this case, a​  The DEFAULT constraint can also be used to insert system values, by using functions like GETDATE (): CREATE TABLE Orders ( ID int NOT NULL, OrderNumber int NOT NULL, OrderDate date DEFAULT GETDATE () SQL DEFAULT on ALTER TABLE. To create a DEFAULT constraint on the "City" column when the table is already created, use the following SQL: