Cascade Delete Problem ...
Posted in 1998
Hi ALL !! I have a table A (parent table) whose primary key is refered by other tables B,C,D,E,F (child table) as foreign key. I want to delete some rows form table A and all dependent row in child tables. I could not find a DELETE SYNTAX like : DELETE CASCADE from parent_table (as in ORACLE) the only way I could figure out is to alter table and set DELETE RULE as ON DELETE CASCADE.But I can;t do it due to some reason. Another way is to find out all child table and write SQL script and manually run it. Then I thought its better to write a ESQL/C routine which will accept name of parent table and form Catalog Tables find out delendent table and delete dependent row,but I got stuck in between b'cause. I could find out *All child table from SYSREFERENCES and SYSTABLES *Key of Parent table on which child depend But could not find out *Columns of child tables refering the parent key Can anybody tell me where (In which Catalog Table) I can get this info ?? Is there any other way to DELETE CASCADE in Informix ?? I am using IDS 7.1 on Digital UNIX. Thanks. Sunil