sql - 条件唯一约束

Question

我有一个购物 list 应用程序，用户可以在其中将任意数量的项目拖放到列表中。项目按二维顺序组织，其中每个项目都有一个整数位置，即索引。与此域一样，任何两个项目都不能具有相同的位置。

目前如下表所示:

  id    list_id    name    position
------------------------------------
  1        1       apple      0
  2        1       banana     1
  3        1       orange     2
  4        2       milk       3      

list_id 是另一个表 (lists) 的外键。

此表具有以下唯一约束:

ALTER TABLE public.items
    ADD CONSTRAINT unique_position 
      UNIQUE (list_id, position)
      DEFERRABLE INITIALLY IMMEDIATE;

这有助于保持数据完整性，并防止应用程序中的错误(或顽皮的用户)导致两个项目被插入到完全相同的位置。 (它是 DEFERRABLE 的原因是需要将约束推迟到包含多个 UPDATE position 语句的事务结束。)

现在我想添加一个功能，用户可以借此在主列表中创建子列表，并将项目插入到这些子列表中，同样以顺序方式进行。

我的计划是向原始 items 表添加一个 sublist_id 列:

  id    list_id   name      position    sublist_id   
-----------------------------------------------------
  1        1      apple        0           NULL      
  2        1      banana       1           NULL      
  3        1      orange       2           NULL      
  4        2      milk         0           NULL      
  5        2      chicken      1           NULL      
  6        2      eggs         2           NULL     
  7        2      coke        NULL          1        
  8        2      water       NULL          1        

并让该引用称为 sublists 的第二个表:

  id    list_id   name   position_in_list
------------------------------------------
  1        2     drinks         3

然后使用名为 sublist_items 的第三个表连接两个表:

  id    sublist_id   item_id   position
----------------------------------------
  1        1            7          0
  1        1            8          1

问题在于 items 表会将一些项目位置列为 NULL，因为这些项目现在位于子列表中，跟踪子列表在其中的位置就足够了主列表，以及子列表中项目的位置。

问题 1:如何更改原始约束以将其应用于两个表？换句话说，我仍然希望列表中的每个项目(无论是 item 还是 sublist)都占据一个唯一的位置，除了 position 值来自 items 和 sublist_items 表，list_id 是通用的。

问题 2: 是否有更好的方法来组织数据？我知道我在技术上不需要第一个表中的 sublist_id 列，但我想我会添加它以便它允许我编写额外的约束，例如“仅当 sublist_id 不为 null 时才允许 position 为 null”。

Answer 1

这是一个有趣的问题:)

有很多方法可以做到这一点。按照我的说法，您可以按照以下步骤操作:

1. 创建一个dummy sublist_id(对于没有任何sublist_id 的项目纯粹出于技术目的)。
2. 规范化您现有的表格:

Sublists(Sublist_id,Name) (Sublist_id as PK)
Items(item_id,sublist_id,name) (Will have Sublist_id as not null foreign key from Sublist table) (item_id as PK)
Items_position(item_id,sublist_id, list_id,position) (you can add check constraint to check that combination of item_id and sublist_id should be available in Items table)(unique constraint for the combination of list_id and position)

1. 现在您可以开始填写数据了:

子列表:

Sublist_Id         Name
    0              Dummy
    1              Drinks

项目:

Item_Id       Sublist_id      Name      
   1               0          Apple
   2               0          Eggs
   3               1          Water
   4               1          Orange

元素位置:

 Item_id      Sublist_id     List_id     Position
    1             0             1           1
    3             1             1           0
    2             0             2           1
    4             1             3           1

如果我遗漏了什么，请告诉我。