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

> > >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.
>
>All of our batch inserts are done without altering any of the metadata of
>the database.

OK, so that's not how it happened.

>I don't see how you can re-create the PK on the table when there is data in
>there that violates the PK. I tried the process you suggested and got the
>message "attempt to store duplicate values (visible to active transactions)
>in unique index "AA"". Now I have a table with multiple duplicate PK's but
>with no PK constraint on them.

Having deleted your database (after meddling with it and totally corrupting
it) I've restored it again from the zipfile, dropped the constraint,
re-added the rogue records and tried to recreate the constraint. I
can't. That's to say, I can't reproducibly cause duplicate keys to appear
in your database by the method I described in the previous posting.

Starting again with a fresh unzip, I dropped the PK and then, without
adding any more rogue records, I tried to recreate the PK. Again, not
allowed.

So that seems to suggest that "something else" occurred between the
insertion of the first Seaside Home loans record (
"327 11030 ","Seaside Home Loans",,

and the second,
"327 11030 ","Seaside Home Loans","",""

Does the fact that the first has nulls in fields 3 and 4, while the second
has empty strings, ring any bells as to what means were used to insert the
records? Did you increase the size of the key between the first and the
second? Do you have more than one application inserting records into the
database?

>Can you please email me this database? I would like to have a look at it.

I couldn't, even if I still had it. I'm on a slow dialup here. But if I
can manage to reproduce what I did before, I'll email you instructions on
how to do it...

How is this key being created? Is it by a trigger, which you have
zapped? I ask, because a trigger would be expected to behave
consistently; whereas, if you are constructing it in multiple client
applications, one would expect the conversions between data types to play
some part in making the inputs sufficiently inconsistent with one another
to allow the possibility of differences between the values presented to
DSQL and those that are stored on commit.

./heLen