回答

收藏

SQL约束,用于检查另一个表中是否不存在值

技术问答 技术问答 222 人阅读 | 0 人回复 | 2023-09-14

在我的PostgreSQL 9.4 数据库中,我有一个表fields,该表的列name具有唯一值。+ ]2 ]5 B# Q. H. U% ^1 }
我正在创建fields_new具有相似结构(此处不重要)和一列的新表name。我需要一种方法来限制name要插入的值,使其fields_new不存在于中fields.name。. a0 {( N% ~  b- p. C- s2 Y4 I
例如,如果fields.name包含值 ‘color’‘length’ ,则需要防止fields_new.name包含
* Q1 Z/ x" m- s& y; r‘color’‘length’ 值。因此,换句话说,我需要提供name两个表中的列之间没有任何重复的值。约束应该是双向的。
6 m9 w2 v' [  K0 l5 T               
; {  ~( r4 F1 `5 P解决方案:0 A5 J. P# d1 u! A8 `2 Z/ }
               
) ~( m- {9 f/ z1 f" K
5 d8 U, ^# a$ U# j+ ^0 n8 J0 l9 z7 H7 l! b9 l
                仅对新条目强制实施约束 fields_new3 J/ w- ]" o: b- ~4 D% k
CHECK约束应该是 不可变的 ,通常会排除对其他表的任何形式的引用,而这些表本质上是不可变的。  D0 F  ]: L2 N. o6 p
为了允许一些回旋余地(尤其是具有时间功能),STABLE可以容忍一些功能。显然,在具有并发写入访问权限的数据库中,这是不完全可靠的。如果引用表中的行发生更改,则它们可能违反了约束。+ z3 D* Z) \( A& j% h& |
声明 约束的无效性质 NOT
. y( n8 r8 b& x  t8 Q  FVALID
(Postgres; l. I  x/ u+ Q2 r
9.1+)。这样,Postgres也不会尝试在还原过程中强制执行它(这可能注定会失败)。详细信息在这里:* @8 I+ J6 W6 R+ y- {
恢复转储时禁用所有约束和表检查8 c% P6 {0 _% A: `% F8 V

' K( \9 v+ D2 T  T; A- m$ M该约束仅适用于新行。
4 Y8 s" Q9 p# P5 }$ e: P  gCREATE OR REPLACE FUNCTION f_fields_name_free(_name text)# q- ~% m  D  |* K  ?, T
  RETURNS bool AS
4 v% q; J  {( K' r4 v$func$
* Z" ~) ?; G) J+ S, @" `7 f3 OSELECT NOT EXISTS (SELECT 1 FROM fields WHERE name = $1);4 b0 M$ H- w% j/ W6 J0 ]: z
$func$  LANGUAGE sql STABLE;5 g" R. v7 G# d& ?4 L! m
ALTER TABLE fields_new ADD CONSTRAINT fields_new_name_not_in_fields
) G  l1 w& b- Z* tCHECK (f_fields_name_free(name)) NOT VALID;1 O, {0 s* h5 Z
另外,当然,一个UNIQUE或PRIMARY KEY约束上fields_new(name),以及对fields(name)。7 F- v: F& D; @  z8 c3 U+ ]" f2 s
双向执行; `9 u+ Y) ^4 K) }
您可以更进一步,将上述CHECK约束反映在第二张桌子上。当两个事务同时写入两个表时,仍然不能保证避免恶劣的竞争条件。$ Z' R* w6 N$ q- @- [
或者,
. _+ W1 @- y) l- o( Z/ o/ v4 y8 b! i您可以使用触发器手动维护“物化视图”:两name列的并集。在UNIQUE此处添加约束。不像对单个表的相同约束那样坚如磐石:可能存在同时写入两个表的竞争条件。但是可能发生的最坏情况是僵局,迫使事务被回滚。如果所有写操作都级联到“实例化视图”,则不会出现永久违规。* I# K0 f0 Y& |/ D0 P( X
只是,你需要触发器INSERT/ UPDATE/DELETE这两个表。
分享到:
您需要登录后才可以回帖 登录 | 立即注册

本版积分规则