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: Continue reading » “SQL: forcing single row tables integrity”