为简单起见,我的表格如下所示:
id | foreign_key_1 | foreign_key_2 | value
从名称、字段foreign_key_1
和foreign_key_2
引用其他两个表中的 PK 可以清楚地看出。
这里棘手的是,只有其中一个不能同时为空,所以我也有以下限制:
UNIQUE (foreign_key_1),
UNIQUE (foreign_key_2),
CHECK ((foreign_key_1 IS NOT NULL AND foreign_key_2 IS NULL)
OR (foreign_key_1 IS NULL AND foreign_key_2 IS NOT NULL))
这些检查保证了我想要的完整性,但现在我还想通过插入新条目foreign_key_1
或foreign_key_2
在插入新条目之前删除现有条目。
DELETE FROM my_table
WHERE ($1::INTEGER IS NULL OR foreign_key_1 = $1::INTEGER)
AND ($2::INTEGER is NULL OR foreign_key_2 = $2::INTEGER)
最初的想法是只有一个查询可以同时处理这两个问题(foreign_key_1, NULL)
并(NULL, foreign_key_2)
作为参数传递。
主要担心的是执行删除语句可能会成功,而插入可能会失败,然后表将处于无效状态。
似乎在这里使用事务是可行的方法,但从代码组织的角度来看,现在需要进行大量重构才能将对存储库的调用包装在同一事务中。
我的下一个想法是使用 UPSERT 和ON CONFLICT(target) DO UPDATE...
我在这里没有单一约束可用作目标,而且我使用的版本 (Postgres 14) 还没有子句NULLS NOT DISTINCT
。
重申一下,我想有一个交集:
UNIQUE(foreign_key_1)
UNIQUE(foreign_key_2)
CHECK ((foreign_key_1 IS NOT NULL AND foreign_key_2 IS NULL) OR (foreign_key_1 IS NULL AND foreign_key_2 IS NOT NULL))
ON CONFLICT
...以便在执行 UPSERT时可以将其用作目标。这有可能以某种方式实现吗?
总的来说,我的理解是,在这种情况下,选项是:
- 重构并在一个事务中执行两个语句[大量重构]
ON CONFLICT
寻找一种方法使该子句具有单一目标- 想出一个聪明的 CTE,它会先删除现有的行
- 别的东西
你有什么建议?
更新
经过一番思考,我大概可以使用:
CREATE UNIQUE INDEX one_null_idx ON my_table(COALESCE(foreign_key_1, -1), COALESCE(foreign_key_2, -1));
不过,这里也需要保证只有一个非空外键的检查约束。
鉴于外键是串行的并且永远不会是 -1,看起来这可行。你怎么看?
对于 ,您需要一个“conflict_target”
ON CONFLICT DO UPDATE
,因此您的“更新”解决方案是最好的解决方法:...虽然您不能
NULLS NOT DISTINCT
在 Postgres 15 中使用。请参阅:但:
serial
默认情况下,列从不为负。但没有什么能阻止您手动输入负数。CHECK
用约束强制执行。另外,在num_nulls()
处理其他约束时使用更简单的方法:看:
选择
还有另一种选择,在单个查询中使用多个 CTE。看: