upsert of new row with check constraints fails

[cldellow]

(I originally opened this in simonw/datasette-insert#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:

sqlite-utils/sqlite_utils/db.py

Lines 2783 to 2787 in 965ca0d

	sql = "INSERT OR IGNORE INTO [{table}]({pks}) VALUES({pk_placeholders});".format(
	table=self.name,
	pks=", ".join(["[{}]".format(p) for p in pks]),
	pk_placeholders=", ".join(["?" for p in pks]),
	)

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:

• table.upsert_all fails to write rows when not_null is present #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 2376c45 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:

• upsert new rows with constraints, fixes #514 #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.