Index Violation - VFP 9.0 1

[KarenJB]

Hello experts!

I am overseeing a VFP application that was purchased from an outside vendor who is no longer available for support. The application has a reindexing procedure that bombs out with a uniqueness of index EMPNO is violated error. I can replicate that error by running INDEX ON EMPNO + IIF(DELETED(),CIDEMPL,SPACE(5)) TAG EMPNO CANDIDATE from the command prompt. The field CIDEMPL is a primary key. I can't seem to figure out what record(s) are causing the problem. This issue just started happening. Any ideas? I can rebuild the EMPNO index by leaving off the CANDIDATE clause. The problem is that when they do run the reindexing procedure it causes the error and I have to manually rebuild that index.

 

[GriffMG]

I think your index expression is wrong:

EMPNO + IIF(DELETED(),CIDEMPL,SPACE(5))

Two not deleted records with the same empno will cause a violation.
Could it be that the expression should be:

EMPNO + IIF(!DELETED(),CIDEMPL,SPACE(5))


Regards

Griff
Keep [Smile]ing

There are 10 kinds of people in the world, those who understand binary and those who don't.

I'm trying to cut down on the use of shrieks (exclamation marks), I'm told they are !good for you. ​

 

[Olaf Doschke]

You don't understand this expression, do you? The CIDEMPL field is only added when the records are deleted, for non deleted records only the empno is taken, so very likely you undeleted empoyees instead of just packing the table. Now it will be hard to find out which employee records is the correct one for seom empno, likely the last one.

Bye, Olaf.

Olaf Doschke Software Engineering

https://www.doschke.name

 

[Mike Lewis]

Looks like Griff has hit it. If a record is not deleted (which is the case with every record at present), then the key will simply be the Empno followed by five spaces. Since, by definition, Empno is not unique, then the key will also not be unique. Hence the violation.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Olaf Doschke]

I guess the reason for the expression this way is the problem deleted records are still in an index, undeleted employees should not have the same empno, so I'd not change the index expression, an empno should only exist once undeleted and the CIDEMPL only makes those deleted unique and enables to reuse the empno.

If you ask me, an identifier should never be reused, there have to be very strict reasons about that like limited number of digits in a barcode. or magstripe data or whatever other restrictions.

The way you have it, there are a few too many undeleted employees, maybe some were once deleted and then recalled.

Bye, Olaf.

Olaf Doschke Software Engineering

https://www.doschke.name