Updating a single field using `UPDATE table SET field = ? WHERE key = ?;` fails with `table.another_field may not be NULL`

Question

(I'm writing my first Perl DBI program using SQLite)

When trying to update a single field in a record executing the prepared statement

UPDATE table SET field = ? WHERE key = ?;

I get an error saying

table.another_field may not be NULL

It is true that another_field has a NOT NULL restriction, but the record to update has the data filled in already.

Is it a misconception that I can UPDATE a record specifying only the field to set using

SET field = value

Naturally when just wanting to update a single field it seems a kind of overkill to read the existing record using SELECT just to set all those values that aren't to be changed, just because they may not be NULL.

I'm using SQLite 3.93.3 and Perl 5.18.2 on Linux x86_64.

Details

(Originally I thought those details are not important for the problem, but maybe they are, so here are some, also answering questions from the comments.

Table Definition

The table's name is KEYS, the field's name is KEYDATA, and the other_field is KEK_DATA.

CREATE TABLE KEYS (
ID              INTEGER PRIMARY KEY AUTOINCREMENT       NOT NULL,
NAME            VARCHAR(30)                             NOT NULL,
DK_DATA         VARCHAR(128)                            NOT NULL,
KEK_DATA        VARCHAR(128)                            NOT NULL,
KEYDATA         VARCHAR(4096)                           NOT NULL,
CREATOR         VARCHAR(20),
CREATED         DATETIME,
MODIFIED        DATETIME,
LOCKED          DATETIME);

Triggers

I also have two triggers defined (as I'm a beginner, those triggers may be sub-optimal, but you'll guess what they should do:

CREATE TRIGGER KEY_CREATED AFTER INSERT ON KEYS FOR EACH ROW
BEGIN
  UPDATE KEYS SET CREATED = datetime('now') WHERE ID = new.ID;
END;
CREATE TRIGGER KEY_MODIFIED AFTER UPDATE ON KEYS FOR EACH ROW
BEGIN
  UPDATE KEYS SET MODIFIED = datetime('now') WHERE ID = new.ID;
END;

SQL Statements

So what's actually triggering the problem is two SQL statements:

1. SELECT DK_DATA, KEK_DATA FROM KEYS WHERE NAME = ?;: I need to read those fields to be able to update KEYDATA.
2. UPDATE KEYS SET KEYDATA = ? WHERE NAME = ?;: The statement that should update the KEYDATA field.

Sample Record

Finally here is a sample record that causes the problem (don't waste your time decoding the data ;-)):

INSERT INTO KEYS VALUES(62,'2','RVYAAQATAhFkdvSkrEyDbbTUnFHfZWEABAExAAUEMTYABAAxAAcDNTA1Nw==','RVYAAQATDWMY2Y1l1PHWU2qrobycBekABAoyABMM04kRSihvavmUHJi4OzhzugAEADEABQsxNg==','RVYAAQAEADEABAcwABMJ5RVnT9DFN0+naro0L6N7Sg==','proc','2023-03-26 00:06:24','2023-03-26 00:06:24',NULL);

Note: The final NAMEs won't be as silly as 2, but while developing the data model and code it's OK so far.

Error Message

Here is the original DBI::errmsg: KEYS.KEK_DATA may not be NULL

Checking for IS NULL

This is to answer Tim Roberts' question:

sqlite> SELECT COUNT(*) FROM KEYS WHERE KEK_DATA IS NULL;
0

Answer 1

Well, the question turned out to be un-answerable with the information provided, and I could delete it as being invalid; however I'll explain what the problem was:

• The problem was not in SQLite
• The problem was not in DBI

Instead the problem was in Perl code, specifically a problem caused by copying similar code and forgetting to adjust it properly:

Instead of preparing

use constant SQL_PUT_KEY        =>      # update key record
    qq{UPDATE KEYS SET KEYDATA = ? WHERE NAME = ?;};

I was preparing

use constant SQL_ADD_KEY        =>      # insert key record
    qq{INSERT INTO KEYS (NAME, DK_DATA, KEK_DATA, KEYDATA, CREATOR)
VALUES (?, ?, ?, ?, ?);};

but still binding the parameters as if SQL_PUT_KEY were used. Thus, as only (the first) two parameters were bound, KEK_DATA (and the rest) was NULL, actually. So this should explain the error seen.

Still I wonder when Perl's DBI did not warn about placeholders not being bound when executing the statement; if it did, I would have noticed my mistake even before having to ask here.