测试 PostgreSQL 中的任何相关行

Question

我有一个具有相当丰富架构的 PostgreSQL [10.5] 数据库。我有一个CUSTOMER_CONTACT被许多其他表引用的表。的关系是一个混合delete cascade和delete restrict。如果没有任何delete restrict表引用客户联系人，我只想为用户提供删除客户联系人的能力。如果其中任何一个这样做，则无法删除客户联系人。现在，删除总是通过用户界面提供，当我发现相关错误时，它会在运行时失败。如何提前确定是否有任何delete restrict表引用了客户联系人，以便在我知道不允许的情况下隐藏用户界面删除操作？除了手动依次检查每个表之外，还有其他方法吗？

Answer 1

这个想法是一个很大的“尾巴摇狗”的事情。您的业务逻辑不是决定数据库结构，而是试图根据业务结构来决定业务逻辑。

但这仍然是可能的，使用存储过程，例如：

首先，我将创建一些虚拟表：

create table CUSTOMER_CONTRACT (id int PRIMARY KEY );

create table CUSTOMER_CONTRACT_REF (id int PRIMARY KEY,

customer_id int REFERENCES CUSTOMER_CONTRACT(id));

现在到存储过程：

create or REPLACE function can_delete_contract(id int) returns boolean AS $$

BEGIN

  delete from CUSTOMER_CONTRACT c

    where c.ID = 1;

  return true;

EXCEPTION

  WHEN FOREIGN_KEY_VIOLATION then

    return false;

END;

$$ LANGUAGE plpgsql;

如有异常，会自动回滚。

现在进行一些测试：

select can_delete_contract(1); // true

insert into CUSTOMER_CONTRACT values (1);

select can_delete_contract(1); // true

insert into CUSTOMER_CONTRACT values (1);

insert into CUSTOMER_CONTRACT_REF values(1, 1);

select can_delete_contract(1); // false