在我的PostgreSQL 9.4数据库中,我有一个表fields
,其中有一个具有唯一值的列name
。
我正在创建一个具有类似结构的新表fields_new
(此处不重要)和列name
。我需要一种方法来约束要插入fields_new
name
值,使其不会出现在fields.name
中。
例如,如果fields.name
包含值"颜色"和"长度",我需要防止fields_new.name
包含"颜色"或"长度"值。因此,换句话说,我需要提供两个表中的name
列之间没有任何重复值。约束应该是双向的。
仅对fields_new
中的新条目强制实施约束
CHECK
约束应该是不可变的,这通常排除了对其他表的任何类型的引用,这些表本质上是不可变的。
为了留出一些余地(尤其是对于时间函数),STABLE
函数是可以容忍的。显然,这在具有并发写入访问权限的数据库中并不完全可靠。如果引用的表中的行发生更改,则它们可能违反了约束。
通过使其NOT VALID
来声明约束的无效性质 (Postgres 9.1+)。这样,Postgres 也不会在恢复期间尝试强制执行它(这注定会失败)。详情请见此处:
- 还原转储时禁用所有约束和表检查
仅对新行强制实施约束。
CREATE OR REPLACE FUNCTION f_fields_name_free(_name text)
RETURNS bool AS
$func$
SELECT NOT EXISTS (SELECT 1 FROM fields WHERE name = $1);
$func$ LANGUAGE sql STABLE;
ALTER TABLE fields_new ADD CONSTRAINT fields_new_name_not_in_fields
CHECK (f_fields_name_free(name)) NOT VALID;
当然,另外,对fields_new(name)
和fields(name)
的UNIQUE
或PRIMARY KEY
约束。
相关:
- 用于检查远程相关表中的值的约束(通过连接等)
- 更新状态标志以保证其他列有效性的函数?
- 触发器与检查约束
双向实施
您可以更进一步,在第二个表上镜像上述CHECK
约束。当两个事务同时写入两个表时,仍然无法保证不会出现令人讨厌的竞争条件。
或者,您可以使用触发器手动维护"物化视图":两列name
列的并集。在此处添加UNIQUE
约束。不像对单个表的相同约束那样坚如磐石:同时写入两个表可能存在争用条件。但可能发生的最坏情况是僵局迫使事务回滚。如果所有写入操作都级联到"具体化视图",则不会发生永久性冲突。
类似于这个相关答案中的"黑暗面":
- PostgreSQL可以对数组元素进行唯一性约束吗?
只是您需要在两个表上INSERT
/UPDATE
/DELETE
触发器。
我遇到了类似的问题,我想维护每个公司的项目列表,以及所有公司的全局列表。 如果公司编号为 0,则将其视为全局,并且无法为使用该名称的任何公司插入新项目。 以下脚本(基于上述解决方案)似乎有效:
drop table if exists blech;
CREATE TABLE blech (
company int,
name_key text,
unique (company, name_key)
);
create or replace function f_foobar(new_company int, new_name_key text) returns bool as
$func$
select not exists (
select 1 from blech b
where $1 <> 0
and b.company = 0
and b.name_key = $2);
$func$ language sql stable;
alter table blech add constraint global_unique_name_key
check (f_foobar(company, name_key)) not valid;
insert into blech values(0,'GLOB1');
insert into blech values(0,'GLOB2');
-- should succeed:
insert into blech values(1,'LOCAL1');
insert into blech values(2,'LOCAL1');
-- should fail:
insert into blech values(1,'GLOB1');
-- should fail:
insert into blech values(0,'GLOB1');