如何实现一张表有且只有一条记录 implement PostgreSQL table have one and only one row

背景

目标, 创建一个表, 这个表有且只能有一行记录.

例子

测试表,

digoal=> create table one_row (id int unique default 1,info text);  
NOTICE:  CREATE TABLE / UNIQUE will create implicit index "one_row_id_key" for table "one_row"  
CREATE TABLE  

创建delete触发器函数

CREATE FUNCTION cannt_delete ()  
RETURNS trigger  
LANGUAGE plpgsql AS $$  
BEGIN  
   RAISE EXCEPTION 'You can not delete!';  
END; $$;  

创建delete触发器

CREATE TRIGGER cannt_delete  
BEFORE DELETE ON one_row  
FOR EACH ROW EXECUTE PROCEDURE cannt_delete();  

创建truncate触发器

CREATE TRIGGER cannt_truncate  
BEFORE TRUNCATE ON one_row  
FOR STATEMENT EXECUTE PROCEDURE cannt_delete();  

测试 ：

digoal=> insert into one_row values (1,'digoal');  
INSERT 0 1  

1. 不允许删除

digoal=> delete from one_row ;  
ERROR:  You can not delete!  
  
digoal=> select * from one_row ;  
 id |  info    
----+--------  
  1 | digoal  
(1 row)  

2. 允许更新

digoal=> update one_row set info='DIGOAL';  
UPDATE 1  

3. 不允许truncate

digoal=> truncate one_row ;  
ERROR:  You can not delete!  

digoal’s 大量PostgreSQL文章入口