如何在Cloud Spanner中实现软删除下Page表的唯一命名约束？

表结构说明 #

Page表结构如下：

- ID (主键)
- DashboardID (外键，关联所属仪表板)
- Name (页面名称，字符串类型)
- DeletedAt (时间戳类型，NULL表示未删除，非NULL表示已删除)

需求规则 #

需要保证：同一仪表板（相同DashboardID）下，未被删除（DeletedAt IS NULL）的页面名称必须唯一。

合法场景示例 #

-- 场景1：同一仪表板下不同名称的未删除页面
Pages: [(1, 1, "Sales", NULL), (1, 2, "Product", NULL)]

-- 场景2：同一仪表板下，未删除页面与已删除同名页面共存
Pages: [(1, 1, "Sales", NULL), (1, 2, "Sales", "2014-01-01T00:00:00")]

不合法场景示例 #

-- 同一仪表板下存在两个未删除的同名页面，违反唯一性
Pages: [(1, 1, "Sales", NULL), (1, 2, "Sales", NULL)]

Spanner中的实现方案 #

Spanner原生不直接支持PostgreSQL风格的"部分索引"，但可以通过生成列+唯一索引的组合实现需求，具体步骤如下：

步骤1：添加生成列标记活跃状态 #

给Page表新增一个存储型生成列，用来标识记录是否为未删除的活跃状态：

ALTER TABLE Page ADD COLUMN IsActive BOOL AS (DeletedAt IS NULL) STORED;

步骤2：创建带过滤条件的唯一索引 #

创建包含DashboardID、Name和IsActive的唯一索引，仅对活跃记录生效：

CREATE UNIQUE INDEX Page_UniqueActivePageName ON Page(DashboardID, Name, IsActive) WHERE IsActive;

该索引会强制约束：仅当IsActive = TRUE（即DeletedAt IS NULL）时，DashboardID + Name的组合必须唯一；已删除的记录不受此约束，完全符合需求。

内容的提问来源于stack exchange，提问作者David542