Constrained by constraints

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;

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.