primary and candidate index

Primary and candidate index best to avoid double data,
but unfortunately in share environment We ecounter problem.

Because we cannot pack the deleted record, meanwhile index
ignore the state of delete so it still exists in index.
When we add a record with key that has been deleted but
not packed yet, it will violate the unique.

Is there any way to solve this ?

Thanks in advance

Holger
Fri Sep 12 06:18:08 CDT 2003

Hi,

there are several ways to solve this.

One way is to add a filter "for !deleted()" to the indexexpressions of the
primary and canditate keys.
Another way is to recycle the deleted record instead of adding a new record
with the same PK value.

--
Holger Vorberg
MS Visual FoxPro MVP, Germany

"sangeko" <not_this@another.else> schrieb im Newsbeitrag
news:0ca201c3791d$aa9e2810$a101280a@phx.gbl...
> Primary and candidate index best to avoid double data,
> but unfortunately in share environment We ecounter problem.
>
> Because we cannot pack the deleted record, meanwhile index
> ignore the state of delete so it still exists in index.
> When we add a record with key that has been deleted but
> not packed yet, it will violate the unique.
>
> Is there any way to solve this ?
>
> Thanks in advance

Rush
Fri Sep 12 11:34:19 CDT 2003


"Holger Vorberg" <holger.vorberg@t-online.de> wrote in message
news:%23%23DMJASeDHA.392@TK2MSFTNGP12.phx.gbl...
> Hi,
>
> there are several ways to solve this.
>
> One way is to add a filter "for !deleted()" to the indexexpressions of the
> primary and canditate keys.

Bad idea - the FOR clause disables Rushmore optimization.

- Rush

trw7at
Mon Sep 15 11:05:21 CDT 2003

That will work until the user deletes the same record twice.
Then you will get a primary key violation on trying to set
the key to the negative value.

As mentioned by others, the most reliable way is to have
surrogate integer primary keys that are automatically
generated via the default value in the field settings.
Once this is in place and done correctly (which is not
difficult), you will never see another primary key
violation because every record will always have a
unique key value - whether deleted or not.

-- TRW

Anders Altberg seemed to utter in news:#tGFfWpeDHA.1700
@TK2MSFTNGP10.phx.gbl:

> Before deleting, change the pk number to -pknumber.
> REPLACE pk WITH -pk
>
> -Anders
>
> "sangeko" <not_this@another.else> wrote in message
> news:0ca201c3791d$aa9e2810$a101280a@phx.gbl...
>> Primary and candidate index best to avoid double data,
>> but unfortunately in share environment We ecounter problem.
>>
>> Because we cannot pack the deleted record, meanwhile index
>> ignore the state of delete so it still exists in index.
>> When we add a record with key that has been deleted but
>> not packed yet, it will violate the unique.
>>
>> Is there any way to solve this ?
>>
>> Thanks in advance
>
>



--
_______________________________________
My e-mail: t r w 7
@ i x . n e t c o m . c o m
_______________________________________

Rush
Mon Sep 15 20:14:10 CDT 2003

Deleting twice? Odd that the user would have that opportunity - but surely;

REPLACE pk WITH -ABS(pk)

is a trivial fix. As far as "most reliable" is concerned, I'm not convinced
that using GUIDs is any less reliable . . . maybe more so.

- Rush

"Tim Witort" <trw7at@ixdot.netcomdotcom> wrote in message
news:Xns93F75CECDA975timwitortwrotethis@207.217.77.203...
> That will work until the user deletes the same record twice.
> Then you will get a primary key violation on trying to set
> the key to the negative value.
>
> As mentioned by others, the most reliable way is to have
> surrogate integer primary keys that are automatically
> generated via the default value in the field settings.
> Once this is in place and done correctly (which is not
> difficult), you will never see another primary key
> violation because every record will always have a
> unique key value - whether deleted or not.
>
> -- TRW
>
> Anders Altberg seemed to utter in news:#tGFfWpeDHA.1700
> @TK2MSFTNGP10.phx.gbl:
>
> > Before deleting, change the pk number to -pknumber.
> > REPLACE pk WITH -pk
> >
> > -Anders
> >
> > "sangeko" <not_this@another.else> wrote in message
> > news:0ca201c3791d$aa9e2810$a101280a@phx.gbl...
> >> Primary and candidate index best to avoid double data,
> >> but unfortunately in share environment We ecounter problem.
> >>
> >> Because we cannot pack the deleted record, meanwhile index
> >> ignore the state of delete so it still exists in index.
> >> When we add a record with key that has been deleted but
> >> not packed yet, it will violate the unique.
> >>
> >> Is there any way to solve this ?
> >>
> >> Thanks in advance
> >
> >
>
>
>
> --
> _______________________________________
> My e-mail: t r w 7
> @ i x . n e t c o m . c o m
> _______________________________________

trw7at
Wed Sep 17 13:19:21 CDT 2003

That won't work, Rush.

Consider a table with the primary key being the "emp_id" field:

Record with ID 317 is created:

emp_id deleted?
------ --------
317 No

Record with ID 317 is deleted, ID changed to -ABS(emp_id)

emp_id deleted?
------ --------
-317 Yes

Record with ID 317 is created (as per the original question)

emp_id deleted?
------ --------
-317 Yes
317 No

Record with ID 317 is deleted, ID changed to -ABS(emp_id)

emp_id deleted?
------ --------
-317 Yes
-317 Yes

Primary key violation.

Leaving the second deleted record positive only delays the
primary key violation to the next record insertion:

Record with ID 317 is deleted, ID unchanged

emp_id deleted?
------ --------
-317 Yes
317 Yes

Record with ID 317 is created

emp_id deleted?
------ --------
-317 Yes
317 Yes
317 No

Primary key violation.

Without knowing the design of the application, who can
say whether any of these scenarios are "odd" or not?
They may be quite common. GUIDs are an alternative to
surrogate integer keys. They're just big and ugly.

-- TRW

Rush Strong seemed to utter in
news:ucTph$#eDHA.1060@TK2MSFTNGP12.phx.gbl:

> Deleting twice? Odd that the user would have that opportunity - but
> surely;
>
> REPLACE pk WITH -ABS(pk)
>
> is a trivial fix. As far as "most reliable" is concerned, I'm not
> convinced that using GUIDs is any less reliable . . . maybe more so.
>
> - Rush
>
> "Tim Witort" <trw7at@ixdot.netcomdotcom> wrote in message
> news:Xns93F75CECDA975timwitortwrotethis@207.217.77.203...
>> That will work until the user deletes the same record twice.
>> Then you will get a primary key violation on trying to set
>> the key to the negative value.
>>
>> As mentioned by others, the most reliable way is to have
>> surrogate integer primary keys that are automatically
>> generated via the default value in the field settings.
>> Once this is in place and done correctly (which is not
>> difficult), you will never see another primary key
>> violation because every record will always have a
>> unique key value - whether deleted or not.
>>
>> -- TRW
>>
>> Anders Altberg seemed to utter in news:#tGFfWpeDHA.1700
>> @TK2MSFTNGP10.phx.gbl:
>>
>> > Before deleting, change the pk number to -pknumber.
>> > REPLACE pk WITH -pk
>> >
>> > -Anders
>> >
>> > "sangeko" <not_this@another.else> wrote in message
>> > news:0ca201c3791d$aa9e2810$a101280a@phx.gbl...
>> >> Primary and candidate index best to avoid double data,
>> >> but unfortunately in share environment We ecounter problem.
>> >>
>> >> Because we cannot pack the deleted record, meanwhile index
>> >> ignore the state of delete so it still exists in index.
>> >> When we add a record with key that has been deleted but
>> >> not packed yet, it will violate the unique.
>> >>
>> >> Is there any way to solve this ?
>> >>
>> >> Thanks in advance
>> >
>> >
>>
>>
>>
>> --
>> _______________________________________
>> My e-mail: t r w 7
>> @ i x . n e t c o m . c o m
>> _______________________________________
>
>
>

--
_______________________________________
My e-mail: t r w 7
@ i x . n e t c o m . c o m
_______________________________________

Rush
Wed Sep 17 14:10:42 CDT 2003

I'm not one to recycle PKs in the first place, so I won't argue in its
favor - but my assumption is that rather than create a "new" ID 317, your PK
generator would first look for a deleted record to recycle, then either
toggle the ID back to positive, or replace it entirely. In any case, it
should not reassign a PK that had already been created - this is the type of
fault that GUIDs avoid!

And while GUIDs are big and ugly, I try not to spend much time looking at
them. Their size was once a concern, but when you look at how dramatically
we see improvements in disk storage space, processor speed, and RAM, I
think that such issues are becoming more and more trivial.

I like the GUID approach most because it eliminates the overhead involved in
assuring uniqueness, which saves me time in development. There may be a
performance advantage in using integers, but I suspect that it is immaterial
in most applications.

- Rush

"Tim Witort" <trw7at@ixdot.netcomdotcom> wrote in message
news:Xns93F973A54FA08timwitortwrotethis@207.217.77.202...
> That won't work, Rush.
>
> Consider a table with the primary key being the "emp_id" field:
>
> Record with ID 317 is created:
>
> emp_id deleted?
> ------ --------
> 317 No
>
> Record with ID 317 is deleted, ID changed to -ABS(emp_id)
>
> emp_id deleted?
> ------ --------
> -317 Yes
>
> Record with ID 317 is created (as per the original question)
>
> emp_id deleted?
> ------ --------
> -317 Yes
> 317 No
>
> Record with ID 317 is deleted, ID changed to -ABS(emp_id)
>
> emp_id deleted?
> ------ --------
> -317 Yes
> -317 Yes
>
> Primary key violation.
>
> Leaving the second deleted record positive only delays the
> primary key violation to the next record insertion:
>
> Record with ID 317 is deleted, ID unchanged
>
> emp_id deleted?
> ------ --------
> -317 Yes
> 317 Yes
>
> Record with ID 317 is created
>
> emp_id deleted?
> ------ --------
> -317 Yes
> 317 Yes
> 317 No
>
> Primary key violation.
>
> Without knowing the design of the application, who can
> say whether any of these scenarios are "odd" or not?
> They may be quite common. GUIDs are an alternative to
> surrogate integer keys. They're just big and ugly.
>
> -- TRW
>
> Rush Strong seemed to utter in
> news:ucTph$#eDHA.1060@TK2MSFTNGP12.phx.gbl:
>
> > Deleting twice? Odd that the user would have that opportunity - but
> > surely;
> >
> > REPLACE pk WITH -ABS(pk)
> >
> > is a trivial fix. As far as "most reliable" is concerned, I'm not
> > convinced that using GUIDs is any less reliable . . . maybe more so.
> >
> > - Rush
> >
> > "Tim Witort" <trw7at@ixdot.netcomdotcom> wrote in message
> > news:Xns93F75CECDA975timwitortwrotethis@207.217.77.203...
> >> That will work until the user deletes the same record twice.
> >> Then you will get a primary key violation on trying to set
> >> the key to the negative value.
> >>
> >> As mentioned by others, the most reliable way is to have
> >> surrogate integer primary keys that are automatically
> >> generated via the default value in the field settings.
> >> Once this is in place and done correctly (which is not
> >> difficult), you will never see another primary key
> >> violation because every record will always have a
> >> unique key value - whether deleted or not.
> >>
> >> -- TRW
> >>
> >> Anders Altberg seemed to utter in news:#tGFfWpeDHA.1700
> >> @TK2MSFTNGP10.phx.gbl:
> >>
> >> > Before deleting, change the pk number to -pknumber.
> >> > REPLACE pk WITH -pk
> >> >
> >> > -Anders
> >> >
> >> > "sangeko" <not_this@another.else> wrote in message
> >> > news:0ca201c3791d$aa9e2810$a101280a@phx.gbl...
> >> >> Primary and candidate index best to avoid double data,
> >> >> but unfortunately in share environment We ecounter problem.
> >> >>
> >> >> Because we cannot pack the deleted record, meanwhile index
> >> >> ignore the state of delete so it still exists in index.
> >> >> When we add a record with key that has been deleted but
> >> >> not packed yet, it will violate the unique.
> >> >>
> >> >> Is there any way to solve this ?
> >> >>
> >> >> Thanks in advance
> >> >
> >> >
> >>
> >>
> >>
> >> --
> >> _______________________________________
> >> My e-mail: t r w 7
> >> @ i x . n e t c o m . c o m
> >> _______________________________________
> >
> >
> >
>
> --
> _______________________________________
> My e-mail: t r w 7
> @ i x . n e t c o m . c o m
> _______________________________________

Rush
Wed Sep 17 17:56:15 CDT 2003

"Tim Witort" <trw7at@ixdot.netcomdotcom> wrote in message
news:Xns93F99E696B4EAtimwitortwrotethis@207.217.77.203...
> Rush Strong seemed to utter in news:OGpRw9UfDHA.956@TK2MSFTNGP09.phx.gbl:
>
> > I'm not one to recycle PKs in the first place, so I won't argue in its
> > favor - but my assumption is that rather than create a "new" ID 317,
> > your PK generator would first look for a deleted record to recycle,
> > then either toggle the ID back to positive, or replace it entirely. In
> > any case, it should not reassign a PK that had already been created -
> > this is the type of fault that GUIDs avoid!
>
> The original poster specifically was asking about how
> to avoid the primary key violation when he creates new
> records that have the same primary key as deleted records.
> In his application (however misguided) the key values are
> reused. In my opinion, this is a huge, huge, huge mistake
> (hence my original recommendation to use surrogate primary
> keys - system generated integers or GUIDs will do). But
> this is the situation he finds himself in. And the
> recommendation to just switch the sign of the primary
> key when you delete the record will not work. That was
> what I wanted to point out. Your solution to his particular
> problem will not work. We can only hope that he will opt
> to replace his primary keys with system-generated surrogate
> keys, because that is the tried and true solution.

'Tis clear that we agree on design philosophy here, but where would my
solution fail? Actually, his solution (if he insists on recycling PKs) is
fairly simple: SET DELETED OFF, SEEK the PK, RECALL if FOUND() AND
DELETED(), APPEND if not, SET DELETED ON.

But I suspect that his design flaws run deeper: the first question we should
have asked is, "Why reuse a PK?" And the answer is probably that the PK has
real meaning, instead of being a surrogate key, which creates the need to
reuse. Case study in what not to do, and why not to do it.

- Rush

trw7at
Fri Sep 19 11:12:11 CDT 2003

Rush Strong seemed to utter in
news:eadqy7WfDHA.3104@TK2MSFTNGP11.phx.gbl:

> "Tim Witort" <trw7at@ixdot.netcomdotcom> wrote in message
> news:Xns93F99E696B4EAtimwitortwrotethis@207.217.77.203...
>> Rush Strong seemed to utter in
>> news:OGpRw9UfDHA.956@TK2MSFTNGP09.phx.gbl:
>>
>> Your solution to his particular
>> problem will not work. We can only hope that he will opt
>> to replace his primary keys with system-generated surrogate
>> keys, because that is the tried and true solution.
>
> 'Tis clear that we agree on design philosophy here, but where would my
> solution fail?

I believe your solution was to replace the primary
key of the record with -ABS(pk) before deleting it.
Was I mistaken? I showed that if the user deletes
the record, makes a new one with the same pk, then
that one gets deleted, a key violation results.
The following bit of code demonstrates the problem:


CREATE TABLE pktest ( ;
pk_field I PRIMARY KEY ;
)

SET DELETED ON

INSERT INTO pktest VALUES(317)

SELECT pktest
LOCATE FOR pk_field = 317
REPLACE pk_field WITH -ABS(pk_field)
DELETE

INSERT INTO pktest VALUES(317)
LOCATE FOR pk_field = 317

*** Primary key violation when this is executed
REPLACE pk_field WITH -ABS(pk_field)
DELETE

-- TRW
_______________________________________
My e-mail: t r w 7
@ i x . n e t c o m . c o m
_______________________________________

Rush
Fri Sep 19 12:03:29 CDT 2003


"Tim Witort" <trw7at@ixdot.netcomdotcom> wrote in message
news:Xns93FB5E18D3B76timwitortwrotethis@207.217.77.205...
> Rush Strong seemed to utter in
> news:eadqy7WfDHA.3104@TK2MSFTNGP11.phx.gbl:
>
> > "Tim Witort" <trw7at@ixdot.netcomdotcom> wrote in message
> > news:Xns93F99E696B4EAtimwitortwrotethis@207.217.77.203...
> >> Rush Strong seemed to utter in
> >> news:OGpRw9UfDHA.956@TK2MSFTNGP09.phx.gbl:
> >>
> >> Your solution to his particular
> >> problem will not work. We can only hope that he will opt
> >> to replace his primary keys with system-generated surrogate
> >> keys, because that is the tried and true solution.
> >
> > 'Tis clear that we agree on design philosophy here, but where would my
> > solution fail?
>
> I believe your solution was to replace the primary
> key of the record with -ABS(pk) before deleting it.
> Was I mistaken? I showed that if the user deletes
> the record, makes a new one with the same pk, then
> that one gets deleted, a key violation results.
> The following bit of code demonstrates the problem:

My solution was to go deeper into the PK generation process. If I was
determined to reuse PKs, my generator would first look for the PK as a
deleted record, and recycle that record if found. IOW, you would never add
a second record with the same PK.

Sound in theory, a looming disaster in practice . . .

- Rush

trw7at
Mon Sep 22 10:37:15 CDT 2003

Rush Strong seemed to utter in
news:uIftAAtfDHA.1060@TK2MSFTNGP12.phx.gbl:
>
> "Tim Witort" <trw7at@ixdot.netcomdotcom> wrote in message
> news:Xns93FB5E18D3B76timwitortwrotethis@207.217.77.205...

>> I believe your solution was to replace the primary
>> key of the record with -ABS(pk) before deleting it.
>> Was I mistaken?
>
> My solution was to go deeper into the PK generation process. If I was
> determined to reuse PKs, my generator would first look for the PK as a
> deleted record, and recycle that record if found. IOW, you would never
> add a second record with the same PK.

You never mentioned RECALLing the deleted record. You
just suggested the -ABS(pk) replacement solution. You
do see that this won't work, don't you? In practice,
it's no different from Anders' code for which you were
presenting it as a fix:

Anders': Before deleting, change the pk number to -pknumber.
REPLACE pk WITH -pk

Yours: Deleting twice? Odd that the user would have that
opportunity - but surely;

REPLACE pk WITH -ABS(pk)

is a trivial fix.

-- TRW
_______________________________________
My e-mail: t r w 7
@ i x . n e t c o m . c o m
_______________________________________

Rush
Mon Sep 22 11:36:16 CDT 2003


"Tim Witort" <trw7at@ixdot.netcomdotcom> wrote in message
news:Xns93FE582D2EF8timwitortwrotethis@207.217.77.203...
> Rush Strong seemed to utter in
> news:uIftAAtfDHA.1060@TK2MSFTNGP12.phx.gbl:
> >
> > "Tim Witort" <trw7at@ixdot.netcomdotcom> wrote in message
> > news:Xns93FB5E18D3B76timwitortwrotethis@207.217.77.205...
>
> >> I believe your solution was to replace the primary
> >> key of the record with -ABS(pk) before deleting it.
> >> Was I mistaken?
> >
> > My solution was to go deeper into the PK generation process. If I was
> > determined to reuse PKs, my generator would first look for the PK as a
> > deleted record, and recycle that record if found. IOW, you would never
> > add a second record with the same PK.
>
> You never mentioned RECALLing the deleted record. You
> just suggested the -ABS(pk) replacement solution. You
> do see that this won't work, don't you? In practice,
> it's no different from Anders' code for which you were
> presenting it as a fix:

From my post on 9/17:

'Tis clear that we agree on design philosophy here, but where would my
solution fail? Actually, his solution (if he insists on recycling PKs) is
fairly simple: SET DELETED OFF, SEEK the PK, RECALL if FOUND() AND
DELETED(), APPEND if not, SET DELETED ON.

[It was after I wrote that that I began to suspect that the original poster
was using "real" instead of surrogate keys].

- Rush

trw7at
Tue Sep 23 11:42:13 CDT 2003

Rush Strong seemed to utter in news:O8JZ7eSgDHA.392@TK2MSFTNGP12.phx.gbl:

>
> "Tim Witort" <trw7at@ixdot.netcomdotcom> wrote in message
> news:Xns93FE582D2EF8timwitortwrotethis@207.217.77.203...
>> Rush Strong seemed to utter in
>> news:uIftAAtfDHA.1060@TK2MSFTNGP12.phx.gbl:
>> >
>> > "Tim Witort" <trw7at@ixdot.netcomdotcom> wrote in message
>> > news:Xns93FB5E18D3B76timwitortwrotethis@207.217.77.205...
>>
>> >> I believe your solution was to replace the primary
>> >> key of the record with -ABS(pk) before deleting it.
>> >> Was I mistaken?
>> >
>> > My solution was to go deeper into the PK generation process. If I
>> > was determined to reuse PKs, my generator would first look for the
>> > PK as a deleted record, and recycle that record if found. IOW, you
>> > would never add a second record with the same PK.
>>
>> You never mentioned RECALLing the deleted record. You
>> just suggested the -ABS(pk) replacement solution. You
>> do see that this won't work, don't you? In practice,
>> it's no different from Anders' code for which you were presenting it
>> as a fix:
>
> From my post on 9/17:
>
> 'Tis clear that we agree on design philosophy here, but where would my
> solution fail? Actually, his solution (if he insists on recycling PKs)
> is fairly simple: SET DELETED OFF, SEEK the PK, RECALL if FOUND() AND
> DELETED(), APPEND if not, SET DELETED ON.
>
> [It was after I wrote that that I began to suspect that the original
> poster was using "real" instead of surrogate keys].
>
> - Rush

Okay. I thought you were referring to your original solution
to simply reverse the sign of the pk field before deleting.
That's the one that would not work. I hope the original
poster now realizes his approach needs to work.

-- TRW
_______________________________________
My e-mail: t r w 7
@ i x . n e t c o m . c o m
_______________________________________