Comments on: SQL: forcing single row tables integrity

By: MySQL SQL Mode Fun – SimonOnSoftware

MySQL SQL Mode Fun – SimonOnSoftware — Thu, 24 Jun 2010 19:56:17 +0000

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

By: Sheeri

Sheeri — Wed, 23 Jun 2010 19:45:02 +0000

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.

By: shlomi

shlomi — Tue, 22 Jun 2010 16:22:17 +0000

@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.

By: William

William — Tue, 22 Jun 2010 16:02:48 +0000

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

By: shlomi

shlomi — Tue, 22 Jun 2010 13:28:38 +0000

@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!

By: Mark R

Mark R — Tue, 22 Jun 2010 09:00:52 +0000

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

By: Roland Bouman

Roland Bouman — Tue, 22 Jun 2010 08:52:58 +0000

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.

By: Roland Bouman

Roland Bouman — Tue, 22 Jun 2010 08:51:17 +0000

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.