CODEWITHSUNDEEP
sqlsqliteperldbi
Garry White
Garry White

Reputation: 189

DBD::SQLite::st execute failed: ID may not be NULL

I try to insert a row using Perl in a table from an SQLite database. The table has a column ID with the AUTOINCREMENT and PRIMARY KEY properties set.

From the MY_TABLE schema, this is all the information regarding the ID field: 

ID [INT AUTO_INCREMENT] NOT NULL,
PRIMARY KEY (ID)

I don't want to configure the ID for each new row added, therefore I tried several ways to let the database to update it itself, but I get:

DBD::SQLite::st execute failed: MY_TABLE.ID may not be NULL

use DBI;
my $db = DBI->connect("...");
my $sql = qq{
    insert into MY_TABLE (ID, col_b, col_c, col_d)
    values(?, ?, ?, ?)
}

my $st = $db->prepare($sql);
$st->execute(undef, 'val2', 'val3', 'val4');

I also tried to skip the parameter completely from the query and from the parameters but same results. Putting 0 instead of undef inserts the actual value (which I don't want to, I want to be incremented automatically).

What am I missing?

Upvotes: 0

Views: 310

Answers (3)

Shawn
Shawn

Reputation: 52439

ID [INT AUTO_INCREMENT] NOT NULL,
PRIMARY KEY (ID)
  1. AUTOINCREMENT (Not AUTO_INCREMEMENT) only works with an INTEGER PRIMARY KEY column (And usually shouldn't be used anyways as it doesn't do what the name implies; details).
  2. Most sqlite tables have a primary key that's an signed 64 bit integer (The rowid). When inserting a new row, if a rowid value is absent or NULL, a new one is generated automatically (This is what people think AUTOINCREMENT does because they're used to how other databases work). An INTEGER PRIMARY KEY column acts an alias for the rowid and behaves the same way. Any other primary key is equivalent to a unique index on that column(s). The column affinity has to be INTEGER for a rowid alias. Nothing else, like INT. More reading.

So, basically, change your table definition to include

id INTEGER PRIMARY KEY

instead and you can leave it out of an INSERT or assign NULL to it and it'll work the way you want.

Upvotes: 1

varro
varro

Reputation: 2482

As elsewhere mentioned, the correct spelling is AUTOINCREMENT, not AUTO_INCREMENT, but never mind about that, what you really need is to declare your ID field INTEGER PRIMARY KEY, exactly like that, INT isn't good enough, and you don't normally need the AUTOINCREMENT keyword anyway (see under https://sqlite.org/lang_createtable.html), and don't specify anything for ID in your INSERT statment.

Upvotes: 1

GMB
GMB

Reputation: 222492

I suspect that you should not pass an empty value to the column, but instead not pass the column itself.

Try :

use DBI;
my $db = DBI->connect("...");
my $sql = qq{
    insert into MY_TABLE (col_b, col_c, col_d)
    values(?, ?, ?)
}

my $st = $db->prepare($sql);
$st->execute('val2', 'val3', 'val4');

Upvotes: 2

Related Questions