Handle a NOT NULL failure explicitly

By Batu · English technical notes

A NOT NULL constraint rejects an SQL NULL for a required column, but an application still needs to decide what to do after rejection. This example creates job(title TEXT NOT NULL) and passes Python None as the bound value. Python’s sqlite3 adapter sends that value as SQL NULL, so the insert raises sqlite3.IntegrityError. The except block turns that known storage failure into the explicit missing_title_rejected outcome.

Before accepting a valid title, the code queries the row count and asserts it is zero. This shows that the failed insert did not create a partial row in this simple case. It then inserts the concrete string "Write tests", retrieves it by id, and asserts the exact returned tuple. The output therefore distinguishes the rejected missing input from the later accepted input, while remaining independent of the database engine’s wording for the exception.

Catching IntegrityError does not tell a user why every possible integrity failure occurred: the same exception class can cover unique, check, foreign-key, and other constraint failures. Production code can validate user-facing requirements before executing SQL, and may inspect a carefully designed schema or error code where supported, but should still expect a constraint exception at the database boundary. This example uses no newer sqlite3 API and runs on Python 3.6+.

The Python sqlite3 documentation covers parameter binding and database exceptions. SQLite defines NOT NULL constraints in its CREATE TABLE documentation.

AI assistance disclosure: This article was drafted with AI assistance and should be tailored to the application’s error-reporting policy.

import sqlite3

con = sqlite3.connect(":memory:")
try:
    con.execute("CREATE TABLE job (id INTEGER PRIMARY KEY, title TEXT NOT NULL)")

    try:
        con.execute("INSERT INTO job(title) VALUES (?)", (None,))
    except sqlite3.IntegrityError:
        missing_title_rejected = True
    else:
        missing_title_rejected = False

    rows_after_failure = con.execute("SELECT COUNT(*) FROM job").fetchone()[0]
    assert missing_title_rejected is True
    assert rows_after_failure == 0

    con.execute("INSERT INTO job(title) VALUES (?)", ("Write tests",))
    stored_title = con.execute("SELECT title FROM job WHERE id = 1").fetchone()
    assert stored_title == ("Write tests",)

    print("missing title: rejected")
    print("rows after failure: 0")
    print("stored title: Write tests")
finally:
    con.close()

missing title: rejected
rows after failure: 0
stored title: Write tests