dimanche 24 janvier 2016

mysql vs sqlite: increment with empty string primary key

Given a simple query

$query = 'INSERT INTO `menu` (`id`,`name`,`value`) VALUES (:id,:name,:value);';

and input params array

$params = [':id' => '', ':name' => 'John', ':value' => 'ABC'];

where id is a PRIMARY KEY and AUTOINCREMENT column. In mysql the id column is autoincremented even if the value passed is an empty string ''; the same does not apply for sqlite since an error is thrown

SQLSTATE[HY000]: General error: 20 datatype mismatch

Question: Is there a way to force sqlite to increment the primary key field when empty string is provided?

Background from docs:

On an INSERT, if the ROWID or INTEGER PRIMARY KEY column is not explicitly given a value, then it will be filled automatically with an unused integer, usually one more than the largest ROWID currently in use.

and

If no ROWID is specified on the insert, or if the specified ROWID has a value of NULL, then an appropriate ROWID is created automatically.

Aucun commentaire:

Enregistrer un commentaire