Ensure SQLite table only has one row

Question

How can I enforce a table to have only one row? Below is what I tried. The UPDATE trigger might work, however, the CREATE trigger definitely will not. For the CREATE, I would like to use SET, however, SET is not supported by SQLite.

CREATE TABLE IF NOT EXISTS `config` (
  `id` TINYINT NOT NULL DEFAULT 0,
  `subdomain` VARCHAR(45) NOT NULL,
  `timezone` CHAR(3) NOT NULL,
  `timeout` TINYINT NOT NULL,
  `offline` TINYINT NOT NULL,
  `hash_config` CHAR(32) NOT NULL,
  `hash_points` CHAR(32) NOT NULL,
  PRIMARY KEY (`id`));

INSERT INTO config(id,subdomain,timezone,timeout,offline,hash_config,hash_points) VALUES(0,'subdomain','UTC',5,0,'hash_config','hash_points');

CREATE TRIGGER `config_insert_zero`
BEFORE INSERT ON `config`
FOR EACH ROW
BEGIN
   -- SET NEW.id=0;
   NEW.id=OLD.id;
END;

CREATE TRIGGER `config_update_zero`
BEFORE UPDATE ON `config`
FOR EACH ROW
BEGIN
   -- SET NEW.id=0;
   NEW.id=OLD.id;
END;

Answer 1

One idea you may want to consider is to make it appear like the table has only one row. In reality, you keep all previous rows because it's quite possible you will one day want to maintain a history of all past values.

Since there is only one row, there really is no need for an ID column, the purpose of which is to uniquely differentiate each row from all the others. You do need, however, a timestamp which will be used to identify the "one row" which will be the latest row written to the table.

CREATE TABLE `config_history` (
  `created`   timestamp default current_timestamp,
  `subdomain` VARCHAR(45) NOT NULL,
  `timezone` CHAR(3) NOT NULL,
  `timeout` TINYINT NOT NULL,
  `offline` TINYINT NOT NULL,
  `hash_config` CHAR(32) NOT NULL,
  `hash_points` CHAR(32) NOT NULL,
  PRIMARY KEY (`created`)
);

Since you are normally interested in only the last row written (the latest version), the query selects the row with the latest creation date:

select ch.created effective_date, ch.subdomain, ch.timezone, ch.timeout,
       ch.offline, ch.hash_config, ch.hash_points
from   config_history ch
where  ch.created =(
       select  max( created )
       from    config_history );

Put a create view config as in front of this query and you have a view that selects only one row, the latest, from the table. Any query against the view returns the one row:

select  *
from    config;

An instead of trigger on the view can convert Updates to Inserts -- you don't actually want to change a value, just write a new row with the new values. This then becomes the new "current" row.

Now you have what appears to be a table with only one row but you also maintain a complete history of all the past changes ever made to that row.

Answer 2

In the general case, to limit the number of rows in a table, you have to prevent any further insert. In SQLite, this is done with RAISE():

CREATE TRIGGER config_no_insert
BEFORE INSERT ON config
WHEN (SELECT COUNT(*) FROM config) >= 1   -- limit here
BEGIN
    SELECT RAISE(FAIL, 'only one row!');
END;

However, if the limit is one, you could instead simply constrain the primary key to a fixed value:

CREATE TABLE config (
    id INTEGER PRIMARY KEY CHECK (id = 0),
    [...]
);