2

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?

Jus12
  • 17,824
  • 28
  • 99
  • 157

2 Answers2

3

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. That's what an RDBMS like Postgres is made for.

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
  • Is it slower to raise a DB exception and catch it or to read balance and check the sum first (needs one additional DB query)? – Jus12 Nov 17 '17 at 05:04
  • 1
    @Jus12: A general answer to that is impossible, it depends on the use case. Raising an exception may be expensive if a big transaction is rolled back with it. – Erwin Brandstetter Nov 17 '17 at 06:59
  • Unfortunately, the same cannot be said for MySQL, as it doesn't support check constraints (and does not throw an error when it sees them). So the use of "reliable" should depend on the DB. – Jus12 Nov 26 '17 at 07:12
  • @Jus12: Definitely, yes. I am speaking for Postgres. If you want "reliable" I wouldn't consider MySQL. – Erwin Brandstetter Nov 26 '17 at 12:17
1

When considering the MVC architecture, we generally build the business logic in the Controller component. We can rely blindly on PostgreSQL check constraint as well for checking the non-zero constraint. Since yours is an application that is dealing with the database, it is better to handle the check constraint within the application rather than at Data Definition Layer.

It gives us more control over delivering the response of an exception and what message to convey.

Neeleshkumar S
  • 746
  • 11
  • 19
  • 1
    Point noted. I'm looking at the most efficient (scalable solution). Using exceptions is likely slower than caching the data myself and doing a check inside the app. – Jus12 Dec 05 '17 at 14:30