16

I recently moved my MySQL database to a new server and it has given me some problems i have newer experienced with MySQL before. My table columns are set to "Default => None" and my table has been generating the default value depending on the Datatype. But now when i try to insert into a table i'm getting this error message: "#1364 - Field 'column_name' doesn't have a default value" and then nothing is inserted into the table.

What can i do to make the "Default" choose it's own value?

NIKO_B
  • 189
  • 1
  • 2
  • 9
  • 1
    Maybe this will help [mysql #1363](http://stackoverflow.com/questions/15438840/mysql-error-1364-field-doesnt-have-a-default-values) – winiardesign Sep 24 '16 at 09:06
  • 1
    You are on a new server now. It has different settings. Most notably `STRICT_TRANS_TABLES ` that is seen in the dupe target close reason above. – Drew Sep 24 '16 at 10:46

1 Answers1

18

It's not saving into the database definitely because the field 'column_name' (and maybe some others) is checked as "NOT NULL". It means that the value of that field must be something other than NULL (NULL - no data at all)

Marking fields as not null is usually a great way to ensure that some data will always be present in the field. Depending on your needs, you can also mark it as NULL so it will never throw an error and will save into DB without the need for anything to be inserted into a specified field.

It means you have 2 options:

  1. Mark your field as NULL (first check if your field is required to have some value or not).

    ALTER TABLE `your_table` 
CHANGE COLUMN `your_field` `your_field` VARCHAR(250) NULL;

  2. Add a default value to the field so if no data is provided on insert, it will put something you defined. For example:

    ALTER TABLE `your_table` CHANGE COLUMN `your_field` `your_field` VARCHAR(250) NOT NULL DEFAULT 'some_default_value';

And of course, match your field type to the field you are going to change.

Dharman
  • 30,962
  • 25
  • 85
  • 135
AwesomeGuy
  • 1,059
  • 9
  • 28
  • Thank you for your answer :) Why do i now have to define the Default value when i didn't have to before. Is there a way to turn it off ? – NIKO_B Sep 24 '16 at 09:37
  • Maybe it was an older version of mysql, or you used a different GUI tool to create your tables which made fields NULL as default. When you mark a field as NULL, it won't require default value nor any kind of value to be inserted. :) – AwesomeGuy Sep 25 '16 at 18:06