Use a CHECK constraint for a bounded score

By Batu · English technical notes

Also published in our primary archive.

A CHECK constraint puts a rule beside the data it governs. Here, score INTEGER NOT NULL CHECK (score BETWEEN 0 AND 100) defines an inclusive range: 0 and 100 are valid, while 101 is not. The program inserts the concrete valid score 88, then uses a parameterized INSERT for 101. The latter raises sqlite3.IntegrityError, which is caught as an expected validation outcome.

The assertions serve two separate purposes. out_of_range_rejected confirms that this particular statement raised the expected database exception. The ordered query confirms the stored result is exactly [(88,)]; therefore the rejected input did not become an additional row in this connection. The fixed prints make the demonstration’s observable result deterministic.

A CHECK expression evaluates to a constraint violation when it is false. In SQLite, a CHECK expression evaluating to NULL does not violate the constraint, which is why this schema also declares score as NOT NULL. This range rule does not establish that an input represents a meaningful assessment, nor does it replace application-level messages or authorization decisions. SQLite’s flexible typing also deserves deliberate schema design when accepting less predictable inputs. The example uses no newer sqlite3 APIs and runs on Python 3.6+.

Read the standard-library connection and IntegrityError reference in the Python sqlite3 documentation, and the SQL constraint details in SQLite’s CREATE TABLE documentation.

AI assistance disclosure: This article was drafted with AI assistance and should be reviewed for the product’s scoring policy.

import sqlite3

con = sqlite3.connect(":memory:")
try:
    con.execute("""
        CREATE TABLE rating (
            score INTEGER NOT NULL CHECK (score BETWEEN 0 AND 100)
        )
    """)
    con.execute("INSERT INTO rating(score) VALUES (?)", (88,))

    try:
        con.execute("INSERT INTO rating(score) VALUES (?)", (101,))
    except sqlite3.IntegrityError:
        out_of_range_rejected = True
    else:
        out_of_range_rejected = False

    scores = con.execute("SELECT score FROM rating ORDER BY score").fetchall()
    assert out_of_range_rejected is True
    assert scores == [(88,)]

    print("accepted score: 88")
    print("score 101: rejected")
    print("stored scores: [(88,)]")
finally:
    con.close()

accepted score: 88
score 101: rejected
stored scores: [(88,)]