时间戳

首页 > 解决方案 > 有条件地限制表字段被引用到另一个表？

sql - 有条件地限制表字段被引用到另一个表？

问题描述

我有一个餐桌吧

CREATE TABLE bar(
  bar_id serial,
  good_bar boolean,
  PRIMARY KEY (bar_id) );

和一张桌子

CREATE TABLE foo (
  foo_id serial,
  side_a INTEGER REFERENCES bar(bar_id),
  side_b INTEGER REFERENCES bar(bar_id) 
  PRIMARY KEY (foo_id);

但是，一旦在 foo.side_a 中使用了“bar”条目，我想阻止它在 foo.side_b 中使用，反之亦然。

PostgreSQL CHECK 约束似乎仅限于放置约束的列，并且复合主键 (side_a, side_b) 不提供诸如

INSERT INTO foo(side_a,side_b) VALUES
(0, 1),
(1, 0);

我想提出某种错误。

我知道我应该能够使用函数 + 触发器来检查插入或更新，但想知道是否没有更简单的解决方案，或者我是否错误地解决了这个问题。

为澄清而编辑：为 bar_id 引用设置单列的问题是，bar 表中的数据将根据分配给哪一侧 bar_id 进行转换（数据库外部）。

我能想到的最好的比喻是棋子。双方将有相同的棋子，但这些棋子的位置可以从白色 (side_a) 的角度和黑色 (side_b) 的角度来考虑。

还有更多的实现细节依赖于数据的两个不同视角的存在。

标签： sqlpostgresqlconstraintsunique

解决方案

You need a unique index on expression:

CREATE UNIQUE INDEX ON foo (greatest(side_a, side_b), least(side_a, side_b));

INSERT INTO foo(side_a,side_b) 
VALUES
    (1, 2),
    (2, 1);

ERROR:  duplicate key value violates unique constraint "foo_greatest_least_idx"
DETAIL:  Key ((GREATEST(side_a, side_b)), (LEAST(side_a, side_b)))=(2, 1) already exists.

推荐阅读