At 11:16 AM 21/10/2004 +1000, you wrote:

>We have come across some data in one of our clients databases that
>violates an integrity constraint, yet has not been picked up by firebird
>or even when a gfix -v -full or gfix -mend -full has been run on the
>database.
>
>The table structure is shown below (from IB_SQL), the data in question
>was ('282 12345', 'Jett Home Loans', NULL, NULL) there were twwo
>rows with this data in the table.
>
>We have seen this only once before, has anyone out there seen this
>before or know of why it could happen?

I'd go for a hard-to-detect missing or extra space character in that
key. This looks like a very horrible choice to use for a key - a definite
case for surrogating the keys, I'd say.

>The client is running FB 1.0.1
>
>CREATE TABLE MBUSERCOMPANY (
> USERCOMPANYKEY CHAR( 20 ) NOT NULL
>....
>, CONSTRAINT INTEG_799
> PRIMARY KEY ( USERCOMPANYKEY )
>)

With this data example, potentially 12 spaces that someone can mess around
with in the parent and child keys. Fun, fun, fun. We can't always protect
our software from user stupidity but we do owe it them and ourselves to try.

./hb