On 2016-03-03 0:52, firebird@... [Firebird-Java]
wrote:

> I am trying to execute this query:
>
> UPDATE SCHED S SET S."Print Order" = (SELECT COALESCE("Print
> Order",0)
> FROM SERVICE V WHERE V.ID = S."Serv ID")
>
> But I get this error:
>
> Context: Statement::Execute( UPDATE SCHED S SET S."Print Order" =
> (SELECT COALESCE("Print Order",0) FROM SERVICE V WHERE V.ID = S."Serv
> ID") )
>
> Message: isc_dsql_execute2 failed
>
> SQL Message : -625
>
> The insert failed because a column definition includes validation
> constraints.
>
> So clearly "Print Order" in the SERVICE table has gotten to be a null
> somehow. So to find this and fix it, I do this query:
>
> SELECT a."Print Order", a."Description" FROM service a where a."Print
> Order" is null
>
> < br>
>
> But no rows are returned. How can that be?
>
> I am running on Windows XP and FlameRobin reports the server version
> to be WI-V2.0.1.12866 Firebird 2.0.
>
> What am I overlooking here?

Your question is a bit off topic on the Firebird-java mailinglist, and
would be on topic on Firebird-support.

But to answer your question: It looks like the column "Print Order"
also has an index, and that index doesn't contain the offending row.
This means that you need to circumvent the optimizer from using the
index. By the looks of your query, "Print Order" is an integer column,
in that case you can use "Print Order" + 0 IS NULL to force the
optimizer to not use the index; if it is a varchar you could use "Print
Order" || '' IS NULL.

Having a corrupted index is not a good sign, and the Firebird version
you use is ancient; a lot of bugs have been fixed since 2.0.1. I
strongly suggest you upgrade to a more recent Firebird version **and**
backup and restore your database to make sure all your indices are
rebuild. If you have reasons to stay with Firebird 2.0 (instead of going
to 2.5.5), then at least upgrade to 2.0.7 as a lot of index-related bugs
were fixed between 2.0.1 and 2.0.7. As always, carefully read all
release notes between the current version and the version you are
upgrading to to make sure you don't run into breaking changes.

Kind regards,
Mark Rotteveel