确保 SQLite 表只有一行 [英] Ensure SQLite table only has one row
本文介绍了确保 SQLite 表只有一行的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
如何强制一个表只有一行?以下是我尝试过的.UPDATE
触发器可能会起作用,但是 CREATE
触发器肯定不会.对于CREATE,我想使用SET
,但是SQLite 不支持SET
.
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;
推荐答案
在一般情况下,要限制表中的行数,您必须防止任何进一步的插入.在 SQLite 中,这是通过 RAISE() 完成的:
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;
但是,如果限制为 1,您可以简单地将主键限制为一个固定值:
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),
[...]
);
这篇关于确保 SQLite 表只有一行的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文