使用函数和检查约束或约束触发器对多个表的外键

Question

我有一个简单的问题。请不要问为什么。

我有两个表，data1 和 data2。它们都有主键 ID 列。保证每个表中的值在两个表中都是唯一的。这是通过检查约束强制执行的。

我有第三个表 (data3(，其中的列应仅包含存在于 data1.id 或 data2.id 中的值。我不能使用常规外键来强制执行此操作。所以我写了一个通过检查约束来做到这一点的函数。

有没有更好的方法来使用触发器约束来做到这一点？

drop schema if exists test cascade;
create schema test;
CREATE FUNCTION test.right_bit_shift()
    RETURNS bigint IMMUTABLE LANGUAGE SQL AS
'SELECT 2::bigint';
create or replace function test.ufn_get_type_id(id bigint) returns bigint as $$
select id >> 2;
$$ language sql
;
create table test.data1(name text);
alter table test.data1 add column id bigint primary key constraint id_chk check(test.ufn_get_type_id(id) =1) no inherit ;
create table test.data2(name text);
alter table test.data2 add column id bigint primary key constraint id_chk check(test.ufn_get_type_id(id) =0) no inherit ;
insert into test.data1(id, name) values(5,'101');
insert into test.data2(id, name) values(1,'001');
create table test.table_lookup(type_id bigint, table_name text);
insert into test.table_lookup(type_id, table_name)
values
(1, 'test.data1'),
(0, 'test.data2');    

create or replace function test.ufn_get_existence_sql(_id bigint) returns text as $$
    select
            'select exists(select 1 from '||table_name||' where id = '||_id||');'
    from test.table_lookup where type_id = test.ufn_get_type_id(_id);
    $$
language sql;

create or replace function test.ufn_id_exists (id_to_check bigint) returns boolean as $$
    declare res bool;
begin
    execute test.ufn_get_existence_sql(id_to_check) into res;
    return res;
end;
$$
    language plpgsql;
create table test.data3(name text, fk_id bigint constraint fk_id check ( test.ufn_id_exists(fk_id) ));

Answer 1

所以我找到了这个

https://dba.stackexchange.com/questions/75613/disable-all-constraints-and-table-checks-while-restoring-a-dump/75635#75635

它指出检查约束应该是不可变的，而我的检查约束当然不是。它可能会导致恢复转储等问题。

因此，似乎最好的方法是插入和更新触发器。

Answer 2

您绝对可以使用触发器执行此操作。定义在插入或更新表 data3 之前触发的触发器，并检查 data3 中的引用列是否存在于 data1.id 或 data2.id 中。如果不是，您可以引发异常。

执行此操作的更简单方法是使用与引用表一样多的列和外键来添加外部约束。在表 3 中，可以添加一个具有对 data2.id 的外键引用的data2_id列，以及另一个具有对 data3.id 的外键引用的列data3_id。这些列必须可为 null，因为虽然data2_id不为 null，但 data3_id 为 null。