What is the pro and con in setting a field as primary or candidate

[Peter]

I will like some pro and con in setting a field as primary or candidate.


Thanks.

[Cindy Winegarden]

Hi Peter,

Both Primary and Candidate indexes ensure unique values, including deleted
records, unless the index has "For Not Deleted()" (or similar) in the index
expression. That means that the database will "do the work" of ensuring
unique values and you won't have to write code (everywhere you add or change
records) to do it.

Since there can be only one Primary index, any other indexes requiring
unique values must be labeled as Candidate indexes.

A disadvantage to Primary and Candidate indexes, if they are for something
like pre-printed tickets, is that the ID value of a deleted record can't be
re-used, unless, of course, you locate the deleted record and recall it.

Some developers like to re-use deleted records. One way around the
uniqueness problem is to blank out the row, set the PK value to -1*TheValue
and then delete it. To recycle the record you can just Set Deleted Off and
Locate for Value < 0.

Hopefully some others will post comments to cover anything I've missed.

[Olaf Doschke]

Post by Cindy Winegarden
Hopefully some others will post comments to cover anything I've missed.

All in all that's about it.

A thing that should be mentioned is, that free tables
don't support primary keys. With them you must
use candidate keys. Don't ask me why that is,
most probably has historical reasons.

With a DBC you only need candidate keys for se-
condary fields or expressions of several fields, that
must be unique, as Cindy said.

The primary key also has it's purpose in defining
relations between tables for generating referential
integrity code. A reference always starts from the
primary key of one table and ends in a correspon-
ding foreign key of another table.

That's the little difference between primary and
candidate.

And then there is PRIMARY() and CANDIDATE()
to determine the type of an existing index, if that
was your real question.

Bye, Olaf.