Adding a new NOT NULL field to a table causes an error

Question

When attempting to add a new column to a table within an update hook, the error Null value not allowed: 1138 Invalid use of NULL value occurred, the definition was changed to allow NULL, and the value for existing rows was set to NULL.

function MYMODULE_update_8101() {
  Drupal::database()->schema()->addField(
    'MYMODULE_table',
    'created',
    [
      'type' => 'int',
      'unsigned' => TRUE,
      'not null' => TRUE,
    ]
  );
}

When adding the column to the database with an SQL query, using the NOT NULL restriction causes the field to have a default value of 0, and no error happens.

How can the error be fixed, and the proper schema set?

Answer 1

If the field spec sets 'not null' => TRUE but does not define a default value, the column is created allowing null values and later altered to disallow them (see the mysql driver Schema::addField(), and the $fixnull variable). The second query attempting add the restriction fails, causing the error.

To fix the error, either

• An 'initial' value needs to be added to the spec provided to addField()

  Drupal::database()->schema()->addField(
    'MYMODULE_table',
    'created',        
    [
      'type' => 'int',
      'unsigned' => TRUE,
      'not null' => TRUE,
      'initial' => 0,
    ]
  )
  

• Or a 'default' value needs to be added to the spec provided to addField().
  When providing a default value, the spec in hook_schema() should also be changed to match.