Candidate Keys & Deleted Records

[Simon Jones]

If a table has a candidate key on a field eg name, a record is entered and
then gets deleted. A new record cannot be entered with the same name until
the table has been packed and the deleted entries removed, even with set
deleted on.

If this statement is correct, what should I be doing to avoid duplicate entries
in the candidate index.

Regards


Simon Jones

[Rick Bean]

Simon,
Names are rarely a good choice for a Candidate key/index - in the real world
you may have two people with the same name. If however you "need" this
feature, then you could
a) do a seek to see if the key exists, and RECALL it for reuse.
b) when you delete it generate a random "name" (e.g. sys(2015) and replace
it so you won't get a conflict.
c) alter the exisisting name before deleting it (e.g. replace first
character with "x") - variant of b)
d) restrict entry until you can do a PACK
e) add a FOR !DELETED() clause to the index - while considered "bad" form
and impossible on most other databases AND it can't be done "visually", it
can be done in code.

Rick

"Simon Jones" <simonj64@hotmail.com> wrote in message
news:3b13cb1d$1@news.devx.com...
>
> If a table has a candidate key on a field eg name, a record is entered and
> then gets deleted. A new record cannot be entered with the same name until
> the table has been packed and the deleted entries removed, even with set
> deleted on.
>
> If this statement is correct, what should I be doing to avoid duplicate
entries
> in the candidate index.
>
> Regards
>
>
> Simon Jones

[Simon Jones]

I received this response from Rick which was helpful.

To further this, I am finding within a data entry form a unique index violation
error called by the database when I am actually deleting a record from the
table. No other data manipulation is being carried out during the delete,
simply it is deleting a record. There are no child relationships. Can there
be a reason for this?

Simon Jones



Subject: Re: Candidate Keys & Deleted Records
Date: Tue, 29 May 2001 14:31:07 -0400
From: "Rick Bean" <rgbean@NOSPAMmelange-inc.com>
Newsgroups: vfp.general

Simon,
Names are rarely a good choice for a Candidate key/index - in the real world
you may have two people with the same name. If however you "need" this
feature, then you could
a) do a seek to see if the key exists, and RECALL it for reuse.
b) when you delete it generate a random "name" (e.g. sys(2015) and replace
it so you won't get a conflict.
c) alter the exisisting name before deleting it (e.g. replace first
character with "x") - variant of b)
d) restrict entry until you can do a PACK
e) add a FOR !DELETED() clause to the index - while considered "bad" form
and impossible on most other databases AND it can't be done "visually", it
can be done in code.

Rick

"Simon Jones" <simonj64@hotmail.com> wrote in message
news:3b13cb1d$1@news.devx.com...
>
> If a table has a candidate key on a field eg name, a record is entered
and
> then gets deleted. A new record cannot be entered with the same name until
> the table has been packed and the deleted entries removed, even with set
> deleted on.
>
> If this statement is correct, what should I be doing to avoid duplicate
entries
> in the candidate index.
>
> Regards
>
>
> Simon Jones

[Nancy Folsom]

In article <3b39fa60$1@news.devx.com>, simonj64@hotmail.com says...
>
> I received this response from Rick which was helpful.
>
> To further this, I am finding within a data entry form a unique index violation
> error called by the database when I am actually deleting a record from the
> table. No other data manipulation is being carried out during the delete,
> simply it is deleting a record. There are no child relationships. Can there
> be a reason for this?

If the table already has a deleted record with that candidate key, then
that is why you'd get the uniqueness error.

You'd need to set it to something else before deleting or, alternately,
recycle the deleted record instead of adding a new one. Strategy depends
a bit on your data.

Some comments on the post you quoted:

> Names are rarely a good choice for a Candidate key/index - in the real world
> you may have two people with the same name. If however you "need" this
> feature, then you could

I agree with this sentiment. I think this is a case where using a
business rule to validate the data makes more sense than using RI. I
don't know what you mean by "name" but for example when the save
happens, you could check for that name and if a record exists ask the
user if they want to edit the existing contact, for example, instead of
adding a new one. (This is how Outlook works, actually, only it checks
email address.)

> a) do a seek to see if the key exists, and RECALL it for reuse.
> b) when you delete it generate a random "name" (e.g. sys(2015) and replace
> it so you won't get a conflict.
> c) alter the exisisting name before deleting it (e.g. replace first
> character with "x") - variant of b

b) is better. This will only delay by one deletion the uniqueness error.

> d) restrict entry until you can do a PACK

Probably not advisable on a production system.

> e) add a FOR !DELETED() clause to the index - while considered "bad" form
> and impossible on most other databases AND it can't be done "visually", it
> can be done in code.

You've found the limitation here. Relying on !DELETED() in candidate and
primary indices only masks the symptom.

--
Nancy
So that all can benefit from the discussion,
please post all followups to the newsgroup.