Check for constraints before deletion

Question

I have a very similar question to this one here, but it was asked 12 years ago and I'm sure things have changed since then.

Basically, I would like to be able to check for foreign key constraints before deleting. I don't want to just do a try/catch or rollback, because I'd like to present the information on the front end to the user to tell them what they need to do in order to be able to delete the item they're trying to remove.

I would like it to be able to continue to work going forward, if new constraints are added.

In a perfect world, I would like to be able to get back a list of primary keys from the rows in the other tables that are dependent on the row being deleted.

As Lasse V. Karlsen pointed out in the aforementioned question this doesn't make much sense. When a user happily decides to delete a row, it could be already bound to another row rendering the deletion impossible. You must do a try/catch anyway because whatever info you collected and presented to a user, it could be changed in a fraction of second the user spends to press OK. — Serg, Jun 11 '21 at 19:59

score 1 · Answer 1 · answered Jun 12 '21 at 09:50

Well, why would you complicate things for users? As far as I understood, you'd want to inform them that the can't delete a master record until they delete detail records first. If that's so, why wouldn't you let the database do it for you (them, that is)? Hint: on delete cascade.

This is what you have now (a very simplified example):

SQL> create table tmaster
  2    (id_mas      number constraint pk_mas primary key);

Table created.

SQL> create table tdetail
  2    (id_det      number constraint pk_det primary key,
  3     id_mas      number constraint fk_det_mas references tmaster (id_mas));

Table created.

SQL> insert all
  2    into tmaster values (1)
  3    into tmaster values (2)
  4    --
  5    into tdetail values (100, 1)   -- references master 1
  6    into tdetail values (101, 1)   -- references master 1
  7    into tdetail values (200, 2)   -- references master 2
  8  select * from dual;

5 rows created.

Deleting master whose details exist won't work:

SQL> delete from tmaster where id_mas = 1;
delete from tmaster where id_mas = 1
*
ERROR at line 1:
ORA-02292: integrity constraint (SCOTT.FK_DET_MAS) violated - child record
found


SQL>

You'd have to

SQL> delete from tdetail where id_mas = 1;

2 rows deleted.

SQL> delete from tmaster where id_mas = 1;

1 row deleted.

SQL>

But, as I said, let the database work. Note line #4 in create table tdetail:

SQL> create table tmaster
  2    (id_mas      number constraint pk_mas primary key);

Table created.

SQL> create table tdetail
  2    (id_det      number constraint pk_det primary key,
  3     id_mas      number constraint fk_det_mas references tmaster (id_mas)
  4                           on delete cascade);                             --> this

Table created.

SQL> insert all
  2    into tmaster values (1)
  3    into tmaster values (2)
  4    --
  5    into tdetail values (100, 1)   -- references master 1
  6    into tdetail values (101, 1)   -- references master 1
  7    into tdetail values (200, 2)   -- references master 2
  8  select * from dual;

5 rows created.

OK, let's delete master (and master only):

SQL> delete from tmaster where id_mas = 1;

1 row deleted.

Whoa, it works! I don't have to do anything about it:

SQL> select * from tmaster;

    ID_MAS
----------
         2

SQL> select * from tdetail;

    ID_DET     ID_MAS
---------- ----------
       200          2

SQL>

Check for constraints before deletion

1 Answers1