"Bill Morrison" <bmorrison@...> wrote in message
news:862A8B31DE3ED211A07E00805FA6307D011EE2A6@walnut...

> st_contains is the child table. The deletes in the parent occur normally.
>
> InterBase will normally never return from trying to read the first entry
> (even letting it run overnight).
>
> While waiting for a response I did some more research and believe I found
> the problem.
>
> st_contains looked like this :
>
> CREATE TABLE ST_CONTAINS(
> SEQ INTEGER NOT NULL,
> DICT_ID INTEGER NOT NULL,
> constraint pk_st_contains primary key(seq,dict_id),
> constraint fk_short_term_seq foreign key (SEQ) references SHORT_TERM

(SEQ),

> constraint fk_short_term_dict_id foreign key (DICT_ID) references

DICTIONARY

> (DICT_ID) on update cascade on delete cascade);

Red light turned on. Emergency. Cascade keyword used. It should be called
"cascade disaster" instead, because I know other reports that blame
cascading FK for db corruption.
Funny enough, a delete-cascade FK generates no more than a user-level
trigger with a name like CHECK_something that fires AFTER deletion, so maybe
something is changed in the way the indices operate in this case. I haven't
checked.

> The deletion for when a short_term entry is deleted is accomplished by a
> trigger (I've been trying to overcome this issue for a while and tried
> moving it from a on delete cascade).

And which manual solution are you using instead of the cascade thing? A
trigger created by yourself or a procedure that does the deletion first in
the child and then in the parent?

> Now, with a "bad" copy of st_contains, if I remove the constraints, then

the

> data becomes accessible again, pointing to something going wrong with the
> indexing.

Then there's a problem in the way indices interact with cascade FKs.

> I am unsure why this behavior exists. I am now trying a run where I only
> have the primary key as a constraint and rely on the triggers to keep the
> data integrity (lousy way to do it, but until an alternative comes
> along...).

Under normal operations, an index will balk immediately when you delete an
entry in the master table whose "slave index" (the FK's index) has an entry.
In this case, the deletion succeeds and after it, the slave table is
cleaned. So, IMHO, some magic happens there.

> If anyone has any additional input on this matter, I'd appreciate hearing
> it. Also, I will try to supply more information if the corruption occurs.

Unfortunately, Bill, you would have to be our guinea pig to discover the
real cause. Maybe it only happens with multiple users connected? I never was
able to reproduce it in the past.

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