"Duilio Foschi" <dedalus@...> wrote in message
news:3.0.1.32.20020323133913.006a06ac@......

> I use Firebird (v. WI-T1.0.0.679).
>
> I want to drop colum ID from table MV_LI, but get the message "there is

one

> dependency".
>
> I cannot find this dependency in my SQL code and this is blocking my work.
>
> Marathon says that the dependency name is DB$717.

This is either a buffer overflow, an array out of bounds or walking over
objects that aren't anymore valid.

> When I look into RDB$DEPENDENCIES I find 4 equal records like this:
>
> rdb$dependent_name rdb$depend_on_name rdb$field_name rdb$dependent_type
> DB$717 MV_LI ID 2
>
> rdb$depended_on_type
> 0
>
> What does it mean ?

It means: MV_LI is a table that sometimes in the past was the detail of some
table that we'll call master. You did:
alter table MV_LI add foreign key(id) references master(m)
on delete [or on update] cascade;

Then the engine created a special trigger IN MASTER (not in MV_LIST), to
delete or update records in MV_LIST to be able to support the CASCADE
option. This trigger has no source code and this is the "source" of the bug
in IB. FB uses special system values for special, automagically created user
triggers that aren't system triggers (but since we have to support IB
databases, you won't like to read the sentences that separate the triggers
in three categories, because they're gruesome).

That special trigger, even without source code, does the following:
- for cascaded delete:
delete from detail where detail.id = old.m;
Here, old is a reference to master.

- for cascaded update:
update detail set id = new.m
where detail.id = old.m;
Here, old and new are references to master, again.

Since the engine tracks every dependency that it knows about (FB tracks
generators and UDFs, IB doesn't), it will create a row in rdb$dependencies,
saying that the automatically created trigger (usually named CHECK_nn)
depends on the field "id" from detail table that's MV_LI in this case. Hence
you get, for example:

rdb$dependent_name=CHECK_50
rdb$depend_on_name=MV_LI
rdb$field_name=ID
rdb$dependent_type=2 => trigger
rdb$depended_on_type=0 => table

However, for unknown reasons, sometimes after the FK is dropped, you get
things like this:
rdb$dependent_name=DB$717
and the rest is correct, although obsolete. Simply put, this record
shouldn't be created. Did you have "on update cascade" and "on delete
cascade" at the same time on that PK? Do you remember if you got an error
message when trying to drop the constraint and you had to commit or
disconnect to retry?

You are another victim of thread synchronization granularity and internal
locks on system objects.
:-)

> How can I delete the column ?

Just drop it directly with a delete statement.

C.
--
Claudio Valderrama C. - http://www.cvalde.com - http://www.firebirdSql.org
Independent developer
Owner of the Interbase® WebRing