Abstract
Informix Dynamic Server (IDS) supports cascading deletes like some other database products. If a row is deleted from a master table which is referenced by a FOREIGN KEY with ON DELETE CASCADE, any matching rows in the detail table are also automatically deleted.
However, there does not appear to be any syntax to enable that without dropping and recreating the foreign key constraint, which requires an exclusive lock on the table and any others linked with referential integrity constraints. Furthermore, if you wanted to implement this on an entire database, that would require significant SQL development.
While standard administrative procedures require dropping and recreating constraints, this article explores an alternative methodology for modifying system catalogs directly. Because this approach bypasses normal engine validation, it is intended for educational purposes and sandbox environments rather than production systems.
Content
The use case that resulted in this article was a customer who wanted to prepare a demo with a clean masked subset of live data. However, deleting unwanted rows was proving very difficult due to referential integrity. Knowing the one place in the system catalog where a flag denoting ON DELETE CASCADE is stored, we experimented successfully with updating that directly.
Examples in this article were produced with the provided stores_demo database in IDS 14.10.FC13W3 Developer Edition from Fix Central, which was the latest at the time, installed in a Rocky 9 Docker container as described here.
The following is the existing foreign key we will use as an example:
This is a non-destructive test to prove that deleting rows from the referenced primary table is not allowed:
That results in this error:
We can enable cascading deletes with:
However, it continues to fail in the same way until we force that table to be reread into the data dictionary:
The same DELETE test as above then works without error.
To do the same on all FOREIGN KEY constraints in the database, first back up current settings:
We can then set all except those on system catalog tables to have ON DELETE CASCADE with:
Reversion would be with:
You are not supposed to have both ON DELETE CASCADE and delete triggers. If you try, you get this error:
Using the trick UPDATE, we can arrange for both to exist, as in the following demonstration.
Firstly, revert the foreign key not to have ON DELETE CASCADE as above.
Then create the following audit table and trigger:
Then prepare test data in a temp table:
Then prove that the trigger works with a test that only affects the audit table:
This row was returned:
| audit_time | 2026-02-20 16:37:46 |
| audit_user | informix |
| audit_event | D |
| customer_num | 121 |
| call_dtime | 2026-02-20 16:37:46 |
| user_id | maryj |
| call_code | O |
| call_descr | Customer likes our merchandise. Requests that we stock more types of infant joggers. Will call back to place order. |
| res_dtime | 2008-07-10 14:06 |
| res_descr | Sent note to marketing group of interest in infant joggers |
Enable ON DELETE CASCADE:
The above trigger test still works, but no rows are returned when we cause cascading deletes:
This proves that, should you use the trick to enable ON DELETE CASCADE, any delete trigger does not fire if a cascading delete occurs, which is probably what we would want.
Caveats
In that state, “dbschema”, “myschema”, or “dbexport” output conflicting SQL, for example:
[informix@ifx ~]$ dbschema -d stores_demo -t cust_calls -q | grep -v revoke | cat -s
{ TABLE "informix".cust_calls row size = 531 number of columns = 7 index size = 31 }
create table "informix".cust_calls
(
customer_num integer,
call_dtime datetime year to minute,
user_id char(32)
default user,
call_code char(1),
call_descr char(240),
res_dtime datetime year to minute,
res_descr char(240),
primary key (customer_num,call_dtime)
);
alter table "informix".cust_calls add constraint (foreign key
(call_code) references "informix".call_type on delete cascade
constraint "informix".r108_20);
alter table "informix".cust_calls add constraint (foreign key
(customer_num) references "informix".customer );
create trigger "informix".del_cust_calls delete on "informix".cust_calls
referencing old as o
for each row
(
insert into "informix".audit_cust_calls (audit_time,audit_user,
audit_event,customer_num,call_dtime,user_id,call_code,call_descr,
res_dtime,res_descr) values (CURRENT year to fraction(3) ,USER ,
'D' ,o.customer_num ,o.call_dtime ,o.user_id ,o.call_code ,o.call_descr
,o.res_dtime ,o.res_descr ));
|
That would fail with error -742 as already described when trying to recreate the table. It is therefore not a good idea to leave such a combination there permanently.
Conclusion
The trick described here can enable ON CASCADE DELETE on a running system with the tables involved fully in use, and provides an easy way to enable it across a whole database.
This methodology provides a low-impact path to enable ON DELETE CASCADE without some of the inherent overheads. As this involves non-standard system modifications, it is intended for testing environments, and future compatibility is not guaranteed.
Related
The following feature request submitted on 25 Mar 2024 is outstanding:
Support for ON DELETE SET NULL and ON UPDATE CASCADE clauses in FOREIGN KEY statements
Disclaimer
Suggestions above are provided “as is” without warranty of any kind, either express or implied, including without limitation any implied warranties of condition, uninterrupted use, merchantability, fitness for a particular purpose, or non-infringement.
Contact Us
If you have any questions or would like to find out more about this topic, please contact us.
