SQL: forcing single row tables integrity

June 22, 2010

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

tags: ,
posted in MySQL by shlomi

« | »

Follow comments via the RSS Feed | Leave a comment | Trackback URL

8 Comments to "SQL: forcing single row tables integrity"

  1. Roland Bouman wrote:

    Or use partitioning:

    CREATE TABLE heartbeat (
    id tinyint unsigned NOT NULL PRIMARY KEY,
    ts datetime NOT NULL
    )
    PARTITION BY RANGE(id) (
    PARTITION P1 VALUES LESS THAN(1)
    );

    (credit due to Guiseppe Maxia, he was the first that I know of to launch the idea to use partitioning to emulate check constraints like this)

    Perhaps a better way to do this is to simply create a table and insert the row as root, then grant select(ts), update(ts) to the application user.

  2. Roland Bouman wrote:

    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.

  3. Mark R wrote:

    You should be able to use CHAR(0), but creating an index on that doesn't appear to be allowed.

  4. shlomi wrote:

    @Roland,
    thank you. Now that you mention it, I recall Giuseppe's post!

    @Mark,
    I have found that out, too. Have commented here in a relevant post of mine. Thank you!

  5. William wrote:

    Ok, integrity is nice and all, but which is the FASTEST Solution?

  6. shlomi wrote:

    @William,
    Fastest solution for what?
    Faster schema to control integrity? I don't think it's of any difference.
    Fastest query type? Read REPLACE INTO: think twice, though with a single row table I haven't benchmarked it.

  7. Sheeri wrote:

    William,

    The fastest solution is not to worry about integrity.

    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.

  8. MySQL SQL Mode Fun – SimonOnSoftware wrote:

    [...] looking for some information on the web, I found quite nice piece of SQL, something [...]

Leave Your Comment

 
Powered by Wordpress and MySQL. Theme by openark.org