sql - 在尝试更改之前如何检查数据库中是否使用了行？

Question

简介

我们有可编辑的实体。如果不使用实体，则可以对其进行完全编辑。如果它在其他地方使用是系统(即数据库中有对行的引用)，则可以对其进行部分编辑并禁止删除。这在后端得到验证，但也应该通过在编辑表单中禁用控件来反射(reflect)在前端。我有一个想法动态填充 MyBatis 映射中的 isInUse 字段。但它看起来有点难看。

示例

这里是 SQL Fiddle .下面是 fiddle 的副本

数据库架构

create table relation_type (
  rt_id bigint primary key, 
  rt_code varchar);
create table entity_type (
  et_id bigint primary key, 
  et_code varchar);
create table entity(
  e_id bigint primary key, 
  et_id bigint references entity_type, 
  et_name varchar);
create table entity_relation (
  er_id bigint primary key, 
  er_type bigint references relation_type, 
  er_source bigint references entity, 
  er_target bigint references entity);

insert into entity_type(et_id, et_code) values (1, 'USER'), (2, 'ORDER');
insert into relation_type(rt_id, rt_code) values (1, 'OWNER'), (2, 'LINK');
insert into entity(e_id, et_id, et_name) values (1, 1, 'user1'), (2, 2, 'Order1'), (3, 2, 'Order2');
insert into entity_relation(er_id, er_type, er_source, er_target) values (1, 1, 1, 2), (2, 1, 1, 3); 

一个可能的解决方案

select 
  rt_id,
  rt_code,
  exists (select 1 from entity_relation where er_type = rt_id limit 1) as rt_is_in_use
from relation_type;  

还有一个

select distinct
  rt_id,
  rt_code,
  (er_id is not null) as rt_is_in_use
from relation_type
left join entity_relation
  on er_type = rt_id;

问题

• 我的方法有什么缺点
• 是否有任何其他方法来检查数据库中是否使用了行？

链接

相关问题及建议使用我用过的东西

• PostgreSql: Get all the rows referencing (via foreign keys) a particular row in a table
• Best way of finding rows referencing a given id on PostgreSQL
• How to find out if rows are refenced from other tables?

找到 this question但这应该在 MyBatis 映射中完成，并作为字段发送到前端。尝试使用诸如 select in select clause 和 postgres check if column used 之类的内容进行谷歌搜索，但结果与我的情况无关。

澄清

• 这些实体将很少被编辑。我相信引入在添加/删除每个链接后递增/递减的计数器列 is_in_use 不是一个好的选择
• 第二种解决方案(使用join)用于从多个表中引用行的情况。在这种情况下，检查使用情况的真正查询类似于

.

SELECT EXISTS (
  SELECT 1
  FROM relation_type 
  JOIN entity_relation
    ON er_type = rt_id
  WHERE rt_id = #{id}

  UNION

  SELECT 1
  FROM relation_type
  JOIN another_table
    ON at_type = rt_id
  WHERE rt_id = #{id}

  UNION

  SELECT 1
  FROM relation_type
  JOIN yet_another_table
    ON yat_type = rt_id
  WHERE rt_id = #{id}
)

Answer 1

解决方案

我想出的最好方法是稍微修改第一个解决方案。由于可以有来自多个表的引用，糟糕的第一个解决方案(select 子句中的子查询)听起来很糟糕。我用的是 MyBatis sql tag带参数。

This element can be used to define a reusable fragment of SQL code that can be included in other statements. It can be statically (during load phase) parametrized. Different property values can vary in include instances

<sql id="columns">
  rt_id,
  rt_code,
  (<include refid="isInUse"><property name="id" value="rt_id"/></include>) AS rt_is_in_use
</sql>

<sql id="baseSelect">
  select
    <include refid="columns"/>

  from relation_type
</sql>

<sql id="isInUse">
  select exists (
    select 1 from entity_relation where er_type = ${id} limit 1
  )
</sql>

<select id="findAll" resultMap="relationTypeMap">
  <include refid="baseSelect"/>
</select>

isInUse sql chunk 也可以接受MyBatis 方法参数。所以可以这样写 select

<select id="isInUse"  resultType="boolean">
  <include refid="isInUse">
    <property name="id" value="#{id}"/>
  </include>
</select>

有用的链接

• Reusing dynamic sql fragments
• How can I reuse an SQL fragment with parameters?