SQL field does not have a default value – Even though field is set to default NULL

I have an older SQL database that I am trying to use, I am not sure of the version it was originally built for but I am trying to use it with Maria 10.2

table1 looks like this…

+-----------+--------------+------+-----+---------+----------------+
| Field     | Type         | Null | Key | Default | Extra          |
+-----------+--------------+------+-----+---------+----------------+
| id        | int(11)      | NO   | PRI | NULL    | auto_increment |
| firstname | varchar(255) | NO   |     | NULL    |                |
| lastname  | varchar(255) | NO   |     | NULL    |                |
+-----------+--------------+------+-----+---------+----------------+

And I am trying to write a firstname to it with a prepared statement like this…

$query = $this->sql->prepare("
   INSERT INTO `table1` (`firstname`)
   VALUES (?)
");

$query->bind_param("s",'john');

I am getting an SQL error…..

[error] => Field 'lastname' doesn't have a default value

Where am I going wrong? It looks like default is set to NULL so doesn’t need me to explicitly set a value.

I have also seen mention of strict_trans_tables being an issue with newer versions but can’t see why this would apply here

Answer

Even though your default value is NULL, the column NULL is set to NO.

Try

ALTER TABLE `table1` MODIFY `lastname` varchar(255) null;

Advertisement

Answer