At 12:01 PM 12/07/2005 +1000, you wrote:

>The database supplied has not been backed up (I tried so that I could reduce
>the size, but to no luck with the restoration).

That's why I mentioned it.

>I can't find any trailing
>blanks on the keys, somehow there is a key in there violating the PK
>constraint!

Confirmed, both of the values there have equal right-padding. (If you
still don't believe that Firebird right-pads foreshortened chars, just use
the IB_SQL export tool to export "select * from mbusercompany.)

>I also tried inserting a row with the key padded out with ' ' (spaces) i.e.
>simular to the example you gave above, this failed with a PK constraint
>violation - this was done in IBSQL, does this trim the values?

In some circumstances, yes it does. Remember, Delphi only knows strings,
not chars and varchars. Unless you specify otherwise, IBO will right-trim
both chars and varchars.

> > I see several problems here. One is the discrepancy between
> > the original and the constituted values. In principle, this
> > wants looking at as a possible bug. We should be able to
> > protect data from bad design.
>
>Sorry, but I don't see how bad design of a PK should be able to violate the
>PK constraints in the database. Even using a char(20) as the PK, if the
>values are different then why does FB choke when doing any operation on that
>key?

Once stored, the chars are blank-padded. Now, to have been able to get
those apparently equal keys in there, either:
-- the client program that stored the second one was able to fool the DSQL
parser that the key value was different (unlikely; but if it happened, and
if you can reproduce it, then it definitely has to be reported as a bug);

or

-- your customer (or perhaps an application program) dropped the PK
constraint temporarily. Now, if the idea behind this was to speed up a
batch insert task, then the app (or the too-smart operator at your customer
site) opened a window where there was nothing to stop duplicates
happening. Rationale: "OK, it's supposed to be a good thing to deactivate
indexes during batch inserts. I can't deactivate the index on the primary
key unless I drop the constraint. Ergo, I'll just drop the
constraint." It looks as if something of that kind actually did
happen. If you look at the data on the two rows, the second one has nulls
in all but the first two columns.

-- the next step would be for your app (or Mr Smart Customer) to recreate
the primary key constraint. There's nothing to stop this. The duplicated
keys won't cause any grief until you come to do something with them.

>I still have no idea how someone (expecially one of our customers ;)) would
>be able to duplicate this scenario, even in a DB admin tool.

Too, too easy. In any query interface:

Alter table mbusercompany drop constraint integ_799 and commit.

Mess around with data and commit it.

Alter table mbusercompany add constraint primary key(usercompanykey) and
commit.

In fact, depending on the tool they were playing about with, they could
have done all of that through a GUI.

>Are you able to reproduce this? If so, I would love to know how.

I'm pleased to say that, with the constraint in place, neither isql nor
IB_SQL allows me to add key values where the only variation is the number
of blanks appended to the second group of digits. That's good, because it
gives at least reasonable protection from ill-conceived key strings.

However, your table on my hard disk now has five records with the primary
key ''327 11030 '. As it happens, I used isql to drop the
constraint, do the damage, and recreate the constraint, logged in as
sysdba. I could not have done that if I had logged in as non-sysba or the
non-owner of the table. Find out who at the customer site knows the sysdba
(or owner) logins and you'll be closer to discovering who has been meddling.

If it were my software, I would be very keen to fix up keys so that they
are atomic and so that, if someone is going to poke around, the worst thing
that could happen is that they create a NEW key that points to duplicate
data. Of course, as long as the metadata is available to meddlers and
dabblers, there's not a lot you can do to prevent them from dropping
constraints....

./heLen