At 08:48 AM 12/07/2005 +1000, you wrote:

>Hi all,
>
>I came across this database this morning. It belongs to a client of ours who
>was reporting some errors when using our software. On inspection of the
>database the table in question seems to have duplicates in the primary key
>column.
>
>Has anyone ever seen this before, or do they know of how it could occur? The
>table does not have any foreign keys and the database is generally only
>accessed by the one connection.

How is this strange PK being assigned? What I see there is a char(20) that
is populated variously as simple digits or (in the case of the problem
record(s), as a mish-mash of numbers and blanks.

On the client side "how it happened" is probably that two different client
apps posted that key and, at the time of posting , they *were*
different. For example, app A posted a key as '327 11020' (no trailing
blanks) while app B posted '327 11020 ' (some trailing blanks). So,
as the two values stood at the time the records were created, they were
different, i.e. the difference between the supplied trailing blanks and the
trailing blanks that would be added to make the length up to 20 was
recognised.

Then, you back up. Gbak will pack those values with all trailing blanks
stripped. Then, when it tries to reconstitute the values on restore, it
pads them both out to 20 with vanilla-flavoured blanks and boom! you've
got duplication.

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.

Another is the choice of a char type to store data of arbitrary size and
format. Char types should be restricted only to store data of known size
and format. Even worse is the choice to use that arbitrary data as a
key. It's no way atomic. It's enough of a problem defining non-simple
keys that have no integrity rules to protect them. It's a disaster waiting
to happen when you expose these keys to users and/or depend on client code
to validate the content.

An inquiry will no doubt reveal that someone at your customer has been
dabbling around in the database with a DB admin tool. There's nothing
quite so effective for finding your design flaws.

./heLen


Now,