Search code examples
sqlpostgresqlh2check-constraints

Can I depend on check constraint to ensure non-negative balance?

I currently have a table as follows:

CREATE TABLE Account (
 ID int NOT NULL,
 Balance int,
 CHECK (Balance>=0)
);

I also have some application pseudo-code as follows:

function updateBalance(id Int, howMuch Int) {
 check howMuch is non zero (can be negative or positive)
 read balance for id as bal
 if bal + howMuch >= 0 update else throw error
}

I feel that reading balance and checking >= 0 is unnecessary and time consuming, since the database I plan to use supports check constraints (most likely PostgreSQL or H2). Certain ones (such as MySQL) don't support check constraints and will silently ignore them in the create statement).

Should I rely on the database to ensure non-negative balance or do it also within my app?

Solution

  • Use a CHECK constraint in the DB. It's absolutely reliable in PostgreSQL.

    You may still want to check input in the app before writing to the DB to avoid raising an exception to begin with, additionally. But you never need to double-check data retrieved from the DB while that CHECK constraint is in place - and VALID. That's what an RDBMS like Postgres is made for.