upsert of new row with check constraints fails

[cldellow]

(I originally opened this in https://github.com/simonw/datasette-insert/issues/20, but I see that that library depends on sqlite-utils)

In the case of a new row, upsert first adds the row, specifying only its pkeys: https://github.com/simonw/sqlite-utils/blob/965ca0d5f5bffe06cc02cd7741344d1ddddf9d56/sqlite_utils/db.py#L2783-L2787

This means that a table with NON NULL (or other constraint) columns that aren't part of the pkey can't have new rows upserted.

[simonw]

Dupe of:

• 538

[simonw]

This means that a table with NON NULL (or other constraint) columns that aren't part of the pkey can't have new rows upserted.

Huh... on that basis, it's possible my fix in https://github.com/simonw/sqlite-utils/commit/2376c452a56b0c3e75e7ca698273434e32945304 is incomplete. I only covered the 'not null' case.

[simonw]

OK, this fails silently:

import sqlite_utils
db = sqlite_utils.Database(memory=True)
db.execute('''CREATE TABLE employees (
    id INTEGER PRIMARY KEY,
    name TEXT,
    age INTEGER,
    salary REAL,
    CHECK (salary is not null and salary > 0)
);''')
db["employees"].upsert({"id": 1, "name": "Bob"}, pk="id")
list(db["employees"].rows)

It outputs:

[]

[simonw]

Applying the fix from the PR here doesn't fix the above problem either:

• https://github.com/simonw/sqlite-utils/pull/515

So it looks like these kinds of check constraints currently aren't compatible with how upsert() works.

[simonw]

Seeing as sqlite-utils doesn't currently provide mechanisms for adding check constraints like this I'm going to leave this - I'm happy with the fix I put in for the not null constraints.