Re: [Firebird-devel] Foreign key error even there should not be one (maybe, Weird problem in one database)

[Tommi P. <tom...@gm...>]

We use
2.5.6.27020 (32bit)

[#1: Delete - PLAN (TOSITE INDEX (PK_TOSITE))]

violation of FOREIGN KEY constraint "FK_VIENTI_TOSITE" on table "VIENTI"
Foreign key references are present for the record
Problematic key value is ("TOSITEID" = 945)

Data is there, it can be selected but the delete fails on this DB

(Hope this helps)

On Thu, Sep 15, 2016 at 10:26 AM, Alex Peshkoff <pes...@ma...> wrote:

> On 15.09.2016 10:14, Tommi Prami wrote:
> > Whole story here :
> >
> > https://plus.google.com/105626236962310493174/posts/3UZB71NjUhA
> >
> > Copy of message
> >   - - - - - - - -  - - - - - - -  -
> > Is there a tool (or way) to debug database step by step.
> >
> > Now we have weird situation where delete from one database fails, on
> > foreign key error, which sounds like it would be trivial thing, but seems
> > so far it is not.
> >
> > I was just thinking that if it would be our application code I would be
> > able to debug through all of the related code and see if there is the
> > culprit or not.
> >
> > We suspect it has somethign to do with specific data on the rows. because
> > some of them delete perfectly fine. This happens in this one database
> only.
> > I've done backup restore for it, I ran gfix stuff on it. I even pumped
> the
> > data in empty DB.
> >
> > it should be problem of database structure (trickers and such), data or
> the
> > Firebird. and most likely it problem of first two.
> >
> > I was just thinking that is there an tool I could see step by step all
> the
> > operations in the DB before the foreigh key error is raised. So I could
> > step by step see the order the trickers and so fires and what they do.
> >
> > Or if someone at Firebird project could check if it is indeed the
> Firebird
> > bug. I think we could send the DB, if it he/she keeps it safe and keeps
> it
> > secret :) 
> >
> > - - - -
> >
> > This is slightly scary. We dropped the FK and created it back again and
> it
> > works like a charm. And friend checked that foreign key from the System
> > tables and it seems to be exactly same.
> >
> > So could this be the bug or issue in firebird after all?
>
> What FB version do you use?
> Modern firebird versions (like FB3 for example) produce good explanation
> what FK failed and why.
> For example (in employee.fdb):
>
> SQL> delete from country where country = 'Japan';
> Statement failed, SQLSTATE = 23000
> violation of FOREIGN KEY constraint "INTEG_11" on table "JOB"
> -Foreign key references are present for the record
> -Problematic key value is ("COUNTRY" = 'Japan')
>
>
> ------------------------------------------------------------
> ------------------
> Firebird-Devel mailing list, web interface at
> https://lists.sourceforge.net/lists/listinfo/firebird-devel
>