Constrained by constraintsPosted: 20 April, 2011
Spending most of my day, as I do, working for the beast (Oracle), it’s not so often I get chance to do any decent Informix work, let alone a bit of coding. So today I spotted an opportunity to brush up on my SPL skills after coming across this error:
892: Cannot disable object (informix.mytableuk01) due to other active objects using it.
As the system was in single user mode at the time because I was doing some maintenance work, this seemed rather puzzling.
The offending code was:
set contraints for mytable disabled;
This command should have disabled all constraints on table mytable and also any referencing constraints. For some reason this did not work.
I began by looking at the output from dbschema and worked my way through but pretty soon I realised that there were an awful lot of dependencies on this table.
So I came up with this function which finds them in no time and may prove useful for someone else:
create function check_fk_dependencies (in_tabname varchar(254), in_level smallint default 0) returning varchar(254) define p_referenced varchar(254); define p_output varchar(254); define p_loop_check varchar(254); define p_level smallint; define p_temp_check varchar(254); on exception in (-206) end exception; -- ignore error on drop of temporary table if already present in session let p_referenced = NULL; let p_level = in_level + 1; if in_level = 0 then drop table fk_loop_check; create temp table fk_loop_check ( tabname varchar(254), parent varchar(254) ) with no log; end if; foreach select p.tabname into p_referenced from sysreferences s, systables r, sysconstraints c, systables p where p.tabid = c.tabid and s.ptabid = r.tabid and s.constrid = c.constrid and p.tabid != r.tabid and r.tabname = in_tabname select tabname into p_loop_check from fk_loop_check where tabname = p_referenced and parent = in_tabname; if p_loop_check is null then insert into fk_loop_check values (p_referenced, in_tabname); foreach execute procedure check_fk_dependencies (p_referenced, p_level) into p_output return in_tabname || ' -> ' || p_output with resume; end foreach; end if; end foreach; if p_referenced is null then return in_tabname; end if; end function;
And there we have it! A function that will show all the dependencies and on which tables you need to disable constraints. It can cope with circular dependencies – that is what the temporary table is used for.