{"id":2523,"date":"2010-06-22T06:58:51","date_gmt":"2010-06-22T04:58:51","guid":{"rendered":"http:\/\/code.openark.org\/blog\/?p=2523"},"modified":"2010-06-22T07:08:55","modified_gmt":"2010-06-22T05:08:55","slug":"sql-forcing-single-row-tables-integrity","status":"publish","type":"post","link":"https:\/\/code.openark.org\/blog\/mysql\/sql-forcing-single-row-tables-integrity","title":{"rendered":"SQL: forcing single row tables integrity"},"content":{"rendered":"

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.<\/p>\n

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?<\/em><\/p>\n

The half-baked solution<\/h4>\n

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

\n
\r\nCREATE TABLE heartbeat (\r\n id int NOT NULL PRIMARY KEY,\r\n ts datetime NOT NULL\r\n );\r\n<\/pre>\n<\/blockquote>\n
The above table definition is taken from mk-heartbeat<\/a>. It should be noted that mk-heartbeat<\/em> 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.<\/p>\n
So, we assume we want this table to have a single row, for whatever reasons we have. We would usually do:<\/p>\n
\n
\r\nREPLACE INTO heartbeat (id, ts) VALUES (1, NOW());\r\n<\/pre>\n<\/blockquote>\n
or<\/p>\n
\n
\r\nINSERT INTO heartbeat (id, ts) VALUES (1, NOW()) ON DUPLICATE KEY UPDATE ts = NOW();\r\n<\/pre>\n<\/blockquote>\n
Why is the above a “half baked solution”<\/em>? Because it is up to the application to make sure it reuses the same PRIMARY KEY<\/strong> value. There is nothing in the database to prevent the following:<\/p>\n
\n
\r\nREPLACE INTO heartbeat (id, ts) VALUES (73, NOW()); -- Ooops\r\n<\/pre>\n<\/blockquote>\n
One may claim that “my application has good integrity”<\/em>. That may be the case; but I would then raise the question: why, then, would you need FOREIGN KEY<\/strong>s<\/em>? Of course, many people don’t use FOREIGN KEY<\/strong>s, but I think the message is clear.<\/p>\n
A heavyweight solution<\/h4>\n
Triggers can help out<\/a>. But really, this is an overkill.<\/p>\n
A solution<\/h4>\n
I purpose a solution where, much like FOREIGN KEY<\/strong>s, the database will force the integrity of the table; namely, have it contain at most one row<\/em>.<\/p>\n
For this solution to work, we will need a strict sql_mode<\/strong>. I’ll show later what happens when using a relaxed sql_mode<\/strong>:<\/p>\n
\n
\r\nSET sql_mode='STRICT_ALL_TABLES'; -- Session scope for the purpose of this article\r\n<\/pre>\n<\/blockquote>\n
Here’s a new table definition:<\/p>\n
\n
\r\nCREATE TABLE heartbeat (\r\n integrity_keeper ENUM('') NOT NULL PRIMARY KEY,\r\n ts datetime NOT NULL\r\n);\r\n<\/pre>\n<\/blockquote>\n
Let’s see what happens now:<\/p>\n
\n
\r\nmysql> INSERT INTO heartbeat (ts) VALUES (NOW());\r\nQuery OK, 1 row affected (0.00 sec)\r\n\r\nmysql> INSERT INTO heartbeat (ts) VALUES (NOW());\r\nERROR 1062 (23000): Duplicate entry '' for key 'PRIMARY'\r\nmysql> INSERT INTO heartbeat (integrity_keeper, ts) VALUES ('', NOW());\r\nERROR 1062 (23000): Duplicate entry '' for key 'PRIMARY'\r\nmysql> INSERT INTO heartbeat (integrity_keeper, ts) VALUES (0, NOW());\r\nERROR 1265 (01000): Data truncated for column 'integrity_keeper' at row 1\r\nmysql> INSERT INTO heartbeat (integrity_keeper, ts) VALUES (1, NOW());\r\nERROR 1062 (23000): Duplicate entry '' for key 'PRIMARY'\r\n\r\nmysql> REPLACE INTO heartbeat (ts) VALUES (NOW());\r\nQuery OK, 2 rows affected (0.00 sec)\r\n\r\nmysql> INSERT INTO heartbeat (ts) VALUES (NOW()) ON DUPLICATE KEY UPDATE ts = NOW();\r\nQuery OK, 0 rows affected (0.00 sec)\r\n\r\nmysql> SELECT * FROM heartbeat;\r\n+------------------+---------------------+\r\n| integrity_keeper | ts\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\r\n+------------------+---------------------+\r\n|\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | 2010-06-15 09:12:19 |\r\n+------------------+---------------------+\r\n<\/pre>\n<\/blockquote>\n
So the trick is to create a PRIMARY KEY<\/strong> column which is only allowed a single value.<\/p>\n
The above shows I cannot force another row into the table: the schema will prevent me from doing so. Mission accomplished.<\/p>\n
Further thoughts<\/h4>\n
The CHECK<\/strong> keyword is the real solution to this problem (and other  problems). However, it is ignored by MySQL.<\/p>\n
It is interesting to note that with a relaxed sql_mode<\/strong>, the INSERT INTO heartbeat (integrity_keeper, ts) VALUES (0, NOW());<\/strong> query succeeds. Why? The default ENUM<\/strong> value is 1<\/strong>, and, being in relaxed mode, 0<\/strong> is allowed in, even though it is not a valid value (Argh!).<\/p>\n","protected":false},"excerpt":{"rendered":"
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 […]<\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"jetpack_post_was_ever_published":false,"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_memberships_contains_paid_content":false,"footnotes":"","jetpack_publicize_message":"","jetpack_publicize_feature_enabled":true,"jetpack_social_post_already_shared":false,"jetpack_social_options":{"image_generator_settings":{"template":"highway","default_image_id":0,"enabled":false},"version":2}},"categories":[5],"tags":[26,21],"class_list":["post-2523","post","type-post","status-publish","format-standard","hentry","category-mysql","tag-indexing","tag-sql"],"jetpack_publicize_connections":[],"jetpack_featured_media_url":"","jetpack_sharing_enabled":true,"jetpack_shortlink":"https:\/\/wp.me\/p2bZZp-EH","_links":{"self":[{"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/posts\/2523","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/comments?post=2523"}],"version-history":[{"count":24,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/posts\/2523\/revisions"}],"predecessor-version":[{"id":2641,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/posts\/2523\/revisions\/2641"}],"wp:attachment":[{"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/media?parent=2523"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/categories?post=2523"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/tags?post=2523"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}