How to check if row is used in database before trying to change it?

Question

Brief

We have editable entities. If an entity is not used it can be fully edited. If it is used elsewhere is the system (e.i. there is reference to row in DB) it can be edited partially and forbidden to remove. This is validated at back-end but also should be reflected on front-end by disabling controls in edit form. I have an idea dynamically fill isInUse field in MyBatis mapping. But it looks a little bit ugly.

Sample

Here is SQL Fiddle. And below is the copy from fiddle

DB schema

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);

One possible solution

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;

and another

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;

Question

what are the drawbacks of my approach
are there any other ways to check if row is used in DB?

Links

Related questions with suggestion to use what I've used

Found this question but this should be done in MyBatis mapping and sent as a field to front-end. Tried google search with something like select in select clause and postgres check if column used but results are not relevant for the my situation.

Clarification

These entities will be rarely edited. I believe introducing counter column is_in_use which is incremented/decremented after adding/removing each link is not a good choice
Second solution (with join) is used when row is referenced from multiple tables. In this case real query to check usage is something like

.

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}
)

score 0 · Accepted Answer · answered Jun 23 '17 at 12:29

Solution

The best way I've came up with is slightly modified first solution. Since there are can be references from several tables, poor first solution (subquery in select clause) sounds bad. I used MyBatis sql tag with parameter.

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 can also accept MyBatis method parameters. So it's possible to write select

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