Single row tables are used in various cases. Such tables can be used for "preferences" or "settings"; for managing counters (e.g. summary tables), for general-purpose administration tasks (e.g. heartbeat table) etc.

The problem with single row tables is that, well, they must have s single row. And the question is: how can you force them to have just one row?

The half-baked solution

The common solution is to create a PRIMARY KEY and always use the same value for that key. In addition, using REPLACE or INSERT INTO ON DUPLICATE KEY UPDATE helps out in updating the row. For example:

The above table definition is taken from mk-heartbeat. It should be noted that mk-heartbeat in itself does not require that the table has a single row, so it is not the target of this post. I'm taking the above table definition as a very simple example.

So, we assume we want this table to have a single row, for whatever reasons we have. We would usually do:

Why is the above a "half baked solution"? Because it is up to the application to make sure it reuses the same PRIMARY KEY value. There is nothing in the database to prevent the following:

REPLACE INTO heartbeat (id, ts) VALUES (73, NOW()); -- Ooops

One may claim that "my application has good integrity". That may be the case; but I would then raise the question: why, then, would you need FOREIGN KEYs? Of course, many people don't use FOREIGN KEYs, but I think the message is clear.

So the trick is to create a PRIMARY KEY column which is only allowed a single value.

The above shows I cannot force another row into the table: the schema will prevent me from doing so. Mission accomplished.

Further thoughts

The CHECK keyword is the real solution to this problem (and other problems). However, it is ignored by MySQL.

It is interesting to note that with a relaxed sql_mode, the INSERT INTO heartbeat (integrity_keeper, ts) VALUES (0, NOW()); query succeeds. Why? The default ENUM value is 1, and, being in relaxed mode, 0 is allowed in, even though it is not a valid value (Argh!).

Oh, wanted to add: you could also use a VIEW WITH CHECK OPTION, but in that case you'd still need to use grants to ensure the underlying table isn't accidentally changed. But if you need grants anyway, then doing proper grants on a table is even simpler.

Speed is not the point of this article, which is why Shlomi did not mention it. In fact, Shlomi doesn't even set the storage engine for the heartbeat table.

As with everything, the fastest solution has many factors: storage engine, what else is happening simultaneously, what else is happening within the rest of the transaction, if this is embedded in a transaction.

William -- just remember, if there was an easy answer, you'd probably already know it.