SQL: forcing single row tables integrity

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:

CREATE TABLE heartbeat (
 id int NOT NULL PRIMARY KEY,
 ts datetime NOT NULL
 );

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:

REPLACE INTO heartbeat (id, ts) VALUES (1, NOW());

or

INSERT INTO heartbeat (id, ts) VALUES (1, NOW()) ON DUPLICATE KEY UPDATE ts = NOW();

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.

A heavyweight solution

Triggers can help out. But really, this is an overkill.

A solution

I purpose a solution where, much like FOREIGN KEYs, the database will force the integrity of the table; namely, have it contain at most one row.

For this solution to work, we will need a strict sql_mode. I’ll show later what happens when using a relaxed sql_mode:

SET sql_mode='STRICT_ALL_TABLES'; -- Session scope for the purpose of this article

Here’s a new table definition:

CREATE TABLE heartbeat (
 integrity_keeper ENUM('') NOT NULL PRIMARY KEY,
 ts datetime NOT NULL
);

Let’s see what happens now:

mysql> INSERT INTO heartbeat (ts) VALUES (NOW());
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO heartbeat (ts) VALUES (NOW());
ERROR 1062 (23000): Duplicate entry '' for key 'PRIMARY'
mysql> INSERT INTO heartbeat (integrity_keeper, ts) VALUES ('', NOW());
ERROR 1062 (23000): Duplicate entry '' for key 'PRIMARY'
mysql> INSERT INTO heartbeat (integrity_keeper, ts) VALUES (0, NOW());
ERROR 1265 (01000): Data truncated for column 'integrity_keeper' at row 1
mysql> INSERT INTO heartbeat (integrity_keeper, ts) VALUES (1, NOW());
ERROR 1062 (23000): Duplicate entry '' for key 'PRIMARY'

mysql> REPLACE INTO heartbeat (ts) VALUES (NOW());
Query OK, 2 rows affected (0.00 sec)

mysql> INSERT INTO heartbeat (ts) VALUES (NOW()) ON DUPLICATE KEY UPDATE ts = NOW();
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT * FROM heartbeat;
+------------------+---------------------+
| integrity_keeper | ts                  |
+------------------+---------------------+
|                  | 2010-06-15 09:12:19 |
+------------------+---------------------+

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!).