mardi 13 octobre 2015

Ensure SQLite table only has one row

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;

Aucun commentaire:

Enregistrer un commentaire