代码之家 › 专栏 › 技术社区 › leetbacoon

检查一个值在每一列中只能存在一次

constraints boolean postgresql sql

leetbacoon · 技术社区 · 5 年前

我们有一张如下的桌子:

=# CREATE TABLE items (item_id INT NOT NULL, revision VARCHAR(2) NOT NULL, is_newest_rev BOOLEAN NOT NULL, UNIQUE (item_id,revision));

=# SELECT * FROM items;
 item_id | revision | is_newest_rev
---------+----------+---------------
     250 |       1A | f
     250 |       1B | t
     199 |       1F | t
      40 |       1A | f     <-note this, false
      40 |       1B | f     <-note this, false
      40 |       1C | t     <-note this, true

上表有效。

下表无效:

=# SELECT * FROM items;
 item_id | revision | is_newest_rev
---------+----------+---------------
     250 |       1A | f
     250 |       1B | t
     199 |       1F | t
      40 |       1A | f     <-note this, false
      40 |       1B | f     <-note this, false
      40 |       1C | t     <-note this, true
      40 |       1D | t     <-ERROR! 40 | 1C is already marked as true!

在将insert查询应用于表之前,我需要创建一个约束来检查它;检查是否只有一个 t item_id 和 revision .

所以,如果我想逃跑 INSERT INTO items VALUES (40, '1D', 't'); 40, 1C 已标记为 t型 . 如果 40 标记为 f ,那么它就会起作用。希望这有意义。

对不起,题目太难听了,我觉得很难说出这个问题的名字。

0 回复 | 直到 5 年前

GMB 5 年前

这可以通过 partial unique index

考虑:

CREATE UNIQUE INDEX items_custom 
    ON items (item_id, is_newest_rev)
    WHERE (is_newest_rev = 't');

Demo on DB Fiddle

CREATE TABLE items (
    item_id INT NOT NULL, 
    revision VARCHAR(2) NOT NULL, 
    is_newest_rev BOOLEAN NOT NULL, 
    UNIQUE (item_id,revision)
);

CREATE UNIQUE INDEX items_custom 
    ON items (item_id, is_newest_rev)
    WHERE (is_newest_rev = 't');

insert into items values(40, '1A', 't');
insert into items values(40, '1B', 'f');
insert into items values(40, '1C', 't');

ERROR:  duplicate key value violates unique constraint "items_custom"
DETAIL:  Key (item_id, is_newest_rev)=(40, t) already exists.

Michael T 5 年前

计算(不存储)应用程序状态

考虑使用顺序数据而不是试图更新状态。

考虑一下即使回复是一个新版本。

 item_id | revision 
---------+----------
     250 |       1
     250 |       2
     199 |       1
      40 |       1
      40 |       2
      40 |       3

或者,为每个修订添加时间戳,如下所示。

 item_id | revision | timestamp
---------+----------+-------------------------
     250 |       1  | 2019-10-16 07:22:00-05

然后,创建如下表:

 CREATE TABLE items (item_id INT NOT NULL, revision INT NOT NULL, *...*, UNIQUE (item_id,revision));

 SELECT * FROM items ORDER BY revision DESC LIMIT 1

另一个选项是测试插入前后的状态。

手动测试状态

你可以指定 项目\u id 修订字段是但这似乎不是你想要的。似乎你想要 是最新版本吗 为复制字段是的价值观。

声明,或语句,并在执行插入之前测试它的值。如果多个进程同时访问数据库,可能也希望在事后这样做。

SELECT COUNT(*) FROM items WHERE item_id = 40 AND is_newest_rev = 't';

Himanshu 5 年前

您可以使用如下触发器

       Create trigger check before  Insert 
        On table for each row as
         If(Select count(*) from (Select 
           item_id,  
         count(distinct is_newest_rev)
         From table where 
        item_id=:new.item_id group by 
          item_id,
           having count(distinct 
            is_newest_rev)=2)>0
         Then
          //dont insert

        Else
          {Insert into......} 
        End if
        End