Duplicated Primary Key 
Author Message
 Duplicated Primary Key

Please help!

Recently I found that a table on my client's network (Novell) have a
duplicated value on it's primary key, I use Integer for the PK.

When I browse the table using SET FILTER TO bond = 1234, it only show the
right one, while there was two records having [bond] with value of 1234.  If
I try to reindex it, error message popup and VFP drop the index tag
automactically.

I want to know, in what situation the above problem will occur?  How to
avoid this?

Futhermore, since the database was planed to upsize to Oracle.  My VFP
program is accessing the database with VFPODBC, will it cause any promble?

Thanks in advance,

Charles



Fri, 07 Mar 2003 02:22:06 GMT  
 Duplicated Primary Key

Charles,

This is not an answer to Oracle but just vfp.

To overcome this you have to take two steps

(1) make any primary/candidate keys on ... for !deleted()

(2) if you depend on that key for sql and other (rushmore) purposes than add
a regular index without the for !deleted() clause since Rushmore cannot use
filtered indexes.

The penalty is just one more index.  But then it will make life easier
--------------------------------------

| Please help!
|
| Recently I found that a table on my client's network (Novell) have a
| duplicated value on it's primary key, I use Integer for the PK.
|
| When I browse the table using SET FILTER TO bond = 1234, it only show the
| right one, while there was two records having [bond] with value of 1234.
If
| I try to reindex it, error message popup and VFP drop the index tag
| automactically.
|
| I want to know, in what situation the above problem will occur?  How to
| avoid this?
|
| Futhermore, since the database was planed to upsize to Oracle.  My VFP
| program is accessing the database with VFPODBC, will it cause any promble?
|
| Thanks in advance,
|
| Charles
|
|
|



Fri, 07 Mar 2003 14:42:16 GMT  
 Duplicated Primary Key
I think you have a deleted record with the same primairy key as the one you
saw.
you can do a few things on this,
You can whenever you create a new record first look if that pk already
exists, recall it and make the record empty.
You can replace the pk with another value when the record is deleted, but
then you must determine which values are free (negative ones) (I don't like
this one)
You can index for not deleted() like Gregory said, but a while ago everyone
in one of these ng's said that indexing on not deleted() is slowing down.
That's because the values don't differ so much. If for example 1 % of your
records are deleted and the others not it will slow down. If for example 40%
of your records are deleted() and 60% not, then the index will not slow
down.

Hope this helps,

Ko Wisse



Quote:
> Charles,

> This is not an answer to Oracle but just vfp.

> To overcome this you have to take two steps

> (1) make any primary/candidate keys on ... for !deleted()

> (2) if you depend on that key for sql and other (rushmore) purposes than
add
> a regular index without the for !deleted() clause since Rushmore cannot
use
> filtered indexes.

> The penalty is just one more index.  But then it will make life easier
> --------------------------------------


> | Please help!
> |
> | Recently I found that a table on my client's network (Novell) have a
> | duplicated value on it's primary key, I use Integer for the PK.
> |
> | When I browse the table using SET FILTER TO bond = 1234, it only show
the
> | right one, while there was two records having [bond] with value of 1234.
> If
> | I try to reindex it, error message popup and VFP drop the index tag
> | automactically.
> |
> | I want to know, in what situation the above problem will occur?  How to
> | avoid this?
> |
> | Futhermore, since the database was planed to upsize to Oracle.  My VFP
> | program is accessing the database with VFPODBC, will it cause any
promble?
> |
> | Thanks in advance,
> |
> | Charles
> |
> |
> |



Fri, 07 Mar 2003 03:00:00 GMT  
 Duplicated Primary Key


Quote:
> You can index for not deleted() like Gregory said, but a while ago
> everyone
> in one of these ng's said that indexing on not deleted() is slowing
> down.

I heard that it was very worthwhile to create an index on deleted()
because whenever VFP issues a SQL...SELECT statement, it will first of all
create an index on deleted() if one does not already exist.

If someone would like to officially clarify this, it would be
illuminating. I think the person who told me about it found out by
examining the sql optimisations ( sys() function??) of VFP 6.



Fri, 07 Mar 2003 03:00:00 GMT  
 Duplicated Primary Key
Gregory,

Thanks for your reply.

It's not the case you thought, both the record found with same field value
was not deleted.

I generate the PK with a PRG, expecting VFP's engine to be the last safe
gaurd, and was disappointed.

As my original message, I want to know what's happen and tries to avoid it
in my own effort (although it may cost me hundred lines of code).

Thanks anyway.

Charles


Quote:

> Charles,

> This is not an answer to Oracle but just vfp.

> To overcome this you have to take two steps

> (1) make any primary/candidate keys on ... for !deleted()

> (2) if you depend on that key for sql and other (rushmore) purposes than
add
> a regular index without the for !deleted() clause since Rushmore cannot
use
> filtered indexes.

> The penalty is just one more index.  But then it will make life easier
> --------------------------------------


> | Please help!
> |
> | Recently I found that a table on my client's network (Novell) have a
> | duplicated value on it's primary key, I use Integer for the PK.
> |
> | When I browse the table using SET FILTER TO bond = 1234, it only show
the
> | right one, while there was two records having [bond] with value of 1234.
> If
> | I try to reindex it, error message popup and VFP drop the index tag
> | automactically.
> |
> | I want to know, in what situation the above problem will occur?  How to
> | avoid this?
> |
> | Futhermore, since the database was planed to upsize to Oracle.  My VFP
> | program is accessing the database with VFPODBC, will it cause any
promble?
> |
> | Thanks in advance,
> |
> | Charles
> |
> |
> |



Fri, 07 Mar 2003 03:00:00 GMT  
 Duplicated Primary Key
Ko,

As my reply to Gregory, I don't think it is the DELETED() key problem
although I've it.

I only index the BOND field as PK, which generated by a routine supposed
with no repeat value.  Every program may have a bug, so I expect the VFP
engine will save me.

Thanks

Charles


Quote:
> I think you have a deleted record with the same primairy key as the one
you
> saw.
> you can do a few things on this,
> You can whenever you create a new record first look if that pk already
> exists, recall it and make the record empty.
> You can replace the pk with another value when the record is deleted, but
> then you must determine which values are free (negative ones) (I don't
like
> this one)
> You can index for not deleted() like Gregory said, but a while ago
everyone
> in one of these ng's said that indexing on not deleted() is slowing down.
> That's because the values don't differ so much. If for example 1 % of your
> records are deleted and the others not it will slow down. If for example
40%
> of your records are deleted() and 60% not, then the index will not slow
> down.

> Hope this helps,

> Ko Wisse



> > Charles,

> > This is not an answer to Oracle but just vfp.

> > To overcome this you have to take two steps

> > (1) make any primary/candidate keys on ... for !deleted()

> > (2) if you depend on that key for sql and other (rushmore) purposes than
> add
> > a regular index without the for !deleted() clause since Rushmore cannot
> use
> > filtered indexes.

> > The penalty is just one more index.  But then it will make life easier
> > --------------------------------------


> > | Please help!
> > |
> > | Recently I found that a table on my client's network (Novell) have a
> > | duplicated value on it's primary key, I use Integer for the PK.
> > |
> > | When I browse the table using SET FILTER TO bond = 1234, it only show
> the
> > | right one, while there was two records having [bond] with value of
1234.
> > If
> > | I try to reindex it, error message popup and VFP drop the index tag
> > | automactically.
> > |
> > | I want to know, in what situation the above problem will occur?  How
to
> > | avoid this?
> > |
> > | Futhermore, since the database was planed to upsize to Oracle.  My VFP
> > | program is accessing the database with VFPODBC, will it cause any
> promble?
> > |
> > | Thanks in advance,
> > |
> > | Charles
> > |
> > |
> > |



Fri, 07 Mar 2003 03:00:00 GMT  
 Duplicated Primary Key

You keep talking about generating the PK in a PRG.  Does this mean that
originally (when the table was empty), there was no PK?  If so, the
generation of a PK only helps prevent duplicate entries after the key
exists, not before.  The reason that you are currently have trouble creating
a PK is that the table is not in a valid state (i.e. there are two or more
records with the same PK value).  Short of removing the duplicate records or
modifying your PK, there's nothing that can be done about it.  Had your PK
already existed before any records were added or modified, then the
duplication should have never been allowed.  If you are saying this is what
happened, then I have no explanation for you.

--
Seairth Jacobs



Quote:
> Gregory,

> Thanks for your reply.

> It's not the case you thought, both the record found with same field value
> was not deleted.

> I generate the PK with a PRG, expecting VFP's engine to be the last safe
> gaurd, and was disappointed.

> As my original message, I want to know what's happen and tries to avoid it
> in my own effort (although it may cost me hundred lines of code).

> Thanks anyway.

> Charles



> > Charles,

> > This is not an answer to Oracle but just vfp.

> > To overcome this you have to take two steps

> > (1) make any primary/candidate keys on ... for !deleted()

> > (2) if you depend on that key for sql and other (rushmore) purposes than
> add
> > a regular index without the for !deleted() clause since Rushmore cannot
> use
> > filtered indexes.

> > The penalty is just one more index.  But then it will make life easier
> > --------------------------------------


> > | Please help!
> > |
> > | Recently I found that a table on my client's network (Novell) have a
> > | duplicated value on it's primary key, I use Integer for the PK.
> > |
> > | When I browse the table using SET FILTER TO bond = 1234, it only show
> the
> > | right one, while there was two records having [bond] with value of
1234.
> > If
> > | I try to reindex it, error message popup and VFP drop the index tag
> > | automactically.
> > |
> > | I want to know, in what situation the above problem will occur?  How
to
> > | avoid this?
> > |
> > | Futhermore, since the database was planed to upsize to Oracle.  My VFP
> > | program is accessing the database with VFPODBC, will it cause any
> promble?
> > |
> > | Thanks in advance,
> > |
> > | Charles
> > |
> > |
> > |



Fri, 07 Mar 2003 03:00:00 GMT  
 Duplicated Primary Key

Steve,

There was an article about this topic in FoxPro Advisor last spring.  The
SYS() function tells you that you have indexes on all of the criteria in
your WHERE clause.  It does not tell you that this is the fastest way.

What FoxPro does when SELECTING records is pull the indexes across the
network, read them, decide which records to retrieve, get them, and then
review the records once more locally to see if there are any further
criteria to apply.  For any field where there were only a small number of
values (deleted, Male/Female, Child/{*filter*}, etc.) an index on that field
would mean that the whole index would be retrieved.

Say you are looking for the women on Elm Street in London.  The cities index
would have a lot of different values, the street index would have a lot of
different values.  Pulling the folks that live on Elm Street in London and
then deciding which are women would be much faster than pulling all of the
women and deciding which live on Elm Street in London.

All of this depends a lot on your particular data and network setup, but
that's the gist of the discussion.

--

Cindy Winegarden
Microsoft Certified Professional, Visual FoxPro

Duke Children's Information Systems
Duke University Medical Center



|
| > You can index for not deleted() like Gregory said, but a while ago
| > everyone
| > in one of these ng's said that indexing on not deleted() is slowing
| > down.
|
| I heard that it was very worthwhile to create an index on deleted()
| because whenever VFP issues a SQL...SELECT statement, it will first of all
| create an index on deleted() if one does not already exist.
|
| If someone would like to officially clarify this, it would be
| illuminating. I think the person who told me about it found out by
| examining the sql optimisations ( sys() function??) of VFP 6.
|
|
|
|



Fri, 07 Mar 2003 03:00:00 GMT  
 Duplicated Primary Key

Quote:

> Say you are looking for the women on Elm Street in London.  The cities
> index
> would have a lot of different values, the street index would have a lot
> of
> different values.  Pulling the folks that live on Elm Street in London
> and
> then deciding which are women would be much faster than pulling all of
> the
> women and deciding which live on Elm Street in London.

Is there any way to specify in which order the index filters should be
applied? I assume the .dbc does not contain enough information for VFP to
determine the best way to apply the index filters? This is where a server
based engine such as SQL server scores I guess.

Also, since indexes are often stored in a single .CDX, does it not mean
that VFP will anyway bring the whole CDX over? So apart from slight speed
gains at the workstation, the network traffic will not be so different?

Steve.



Sat, 08 Mar 2003 03:00:00 GMT  
 Duplicated Primary Key
Seairth,
Quote:
----- Original Message -----

Newsgroups: microsoft.public.fox.vfp.dbc
Sent: Tuesday, September 19, 2000 1:50 AM
Subject: Re: Duplicated Primary Key

> You keep talking about generating the PK in a PRG.  Does this mean that
> originally (when the table was empty), there was no PK?

There was originally a PK on the involved table, which had been running for
more then ONE year.

>  If so, the generation of a PK only helps prevent duplicate entries after
the key
> exists, not before.

That's what I expect VFP do, and I was disappointed.

> The reason that you are currently have trouble creating
> a PK is that the table is not in a valid state (i.e. there are two or more
> records with the same PK value).

That's what I want to know, in what situation the PK will loss integrity.

> Short of removing the duplicate records or
> modifying your PK, there's nothing that can be done about it.

I'm not tring to re-use any PK or modify it in any case

> Had your PK
> already existed before any records were added or modified, then the
> duplication should have never been allowed.

My case, I found it.

> If you are saying this is what
> happened, then I have no explanation for you.

Thank you.

> --
> Seairth Jacobs


Thanks for your input.



> > Gregory,

> > Thanks for your reply.

> > It's not the case you thought, both the record found with same field
value
> > was not deleted.

> > I generate the PK with a PRG, expecting VFP's engine to be the last safe
> > gaurd, and was disappointed.

> > As my original message, I want to know what's happen and tries to avoid
it
> > in my own effort (although it may cost me hundred lines of code).

> > Thanks anyway.

> > Charles



> > > Charles,

> > > This is not an answer to Oracle but just vfp.

> > > To overcome this you have to take two steps

> > > (1) make any primary/candidate keys on ... for !deleted()

> > > (2) if you depend on that key for sql and other (rushmore) purposes
than
> > add
> > > a regular index without the for !deleted() clause since Rushmore
cannot
> > use
> > > filtered indexes.

> > > The penalty is just one more index.  But then it will make life easier
> > > --------------------------------------


> > > | Please help!
> > > |
> > > | Recently I found that a table on my client's network (Novell) have a
> > > | duplicated value on it's primary key, I use Integer for the PK.
> > > |
> > > | When I browse the table using SET FILTER TO bond = 1234, it only
show
> > the
> > > | right one, while there was two records having [bond] with value of
> 1234.
> > > If
> > > | I try to reindex it, error message popup and VFP drop the index tag
> > > | automactically.
> > > |
> > > | I want to know, in what situation the above problem will occur?  How
> to
> > > | avoid this?
> > > |
> > > | Futhermore, since the database was planed to upsize to Oracle.  My
VFP
> > > | program is accessing the database with VFPODBC, will it cause any
> > promble?
> > > |
> > > | Thanks in advance,
> > > |
> > > | Charles
> > > |
> > > |
> > > |



Sun, 09 Mar 2003 01:34:48 GMT  
 
 [ 10 post ] 

 Relevant Pages 

1. duplicate primary keys in table

2. duplicate primary keys in table

3. Please Help: Duplicate Primary Keys

4. Duplicate primary keys

5. Corrupt indexes and Duplicate primary keys

6. Buffering & duplicate primary key

7. Primary Keys, Candidate Keys And Record Re-Usage

8. change primary key ro regular key

9. Primary Keys

10. Auto-incrementing a primary key field

11. vfp6 and primary keys - beginner question ;-)

12. Question on primary key?

 

 
Powered by phpBB® Forum Software