“Just-in-time” inserting rows into a database

If you're writing software using a database system such as MySQL, your code doesn't have access to all the data at any point in time, you SELECT just the data you need to operate on.

If you want to do some database-wide operation, you need to ask the database to do it, because you don't have all the rows.

When you want to enforce uniqueness, for example across a whole table (for example a document name needs to be unique), or across a particular part of a table (for example each user must have documents with unique names), you need to ask the database to do it, because, again, you don't have all the rows.

There is only one acceptable way to do this with a SQL database. It happens when you write a new row into a table where a uniqueness constraint must be enforced:

  1. Insert a new row
  2. If the insert succeeds, there wasn't a row there before, and now there is
  3. If the insert throws a unique constraint violation, the row is already there
  4. If you want to update the row (i.e. an "insert or update" operation to maintain a "lazy singleton" in the database), you can update the row with safety after the unique constraint violation, as you can be certain the row is already there.

The following methods are all not acceptable:

The way I program this is the following. On the "insert" statement, I catch the database error, and see if it's a "unique constraint violation"-type error. If it is, I throw an (unchecked) Exception. The calling code can catch that and do something with it (or not, if the statement should not generate such an error, in which case it will propogate to the main loop like any other database exception). I have had the pleasure of introducing this to easyname.eu and now also the pleasure of introducing this to the WebTek framework.

It is extremely frustrating working with big complex frameworks, whose usage is much more complex than just writing SQL manually, and not being able to do the above properly.

I mean checking unique constraints is something every database application needs, so the fact it's not supported by major frameworks is just unbelievable.

P.S. I recently created a nerdy privacy-respecting tool called When Will I Run Out Of Money? It's available for free if you want to check it out.

This article is © Adrian Smith.
It was originally published on 13 Jun 2008
More on: Databases | FAIL | Software Architecture