No. A foreign key cannot use a literal value, nor can it use an expression or a virtual generated column. It must use a column identifier.
FOREIGN KEY <name> (<column1>, <column2>, ...)
REFERENCES <tablename> (<column1>, <column2>, ...)
It looks like you're trying to do polymorphic-associations.
The easiest solution to do ...
Well, I tried with an AFTER DELETE trigger (that showed the same problem), an INSTEAD OF DELETE trigger (ORA-25002: forbidden on tables), and dropping the ON DELETE constraints (ORA-02292: child records found).
I'm ashamed to say I found a way out: same as for MS SQL Server, I dropped all FOREIGN KEY constraints and do all of the logic in two BEFORE DELETE ...
My original python script solution: (semi-manual approach to allow custom order)
from collections import defaultdict
# show full tables where table_type <> "VIEW";
tables = [
This should get you started:
I created a set of tables in a hierarchy, so t1 is parent of t2 and t3, t2 is parent of t4 and t5, t3 is parent of t6, t6 is parent of t7.
with recursive cte as (
select t.table_name, 0 as depth
from information_schema.tables t
left join information_schema.referential_constraints r
on t.table_name = r.table_name