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.

 

[Tore Bleken]

Two deleted records with the same Empno? Or two blank records?

 

[KarenJB]

Thanks for the quick reply! There are no deleted records in the table, it has been packed. Also, I have checked for blanks records and there are none. I even wrote a quick program to make sure the CIDEMPL field did not have a duplicate value.

 

[Mike Lewis]

Just off the top of my head: Does either EMPNO or CIDEMPL contain any NULL values? If so, mulitple NULLs count as duplicates in this context.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[KarenJB]

No null values in those fields.

 

[Tore Bleken]

Check again...! This code will find all records where you have more than one empno with the same data:
[pre]Set Deleted Off
Select empno from yourtable group by 1 having count(*)>1
[/pre]

 

[KarenJB]

I even deleted all the records in the table and ran that index command successfully. When I tried to recall all, it stopped part way through with the unique error again.

 

[Mike Lewis]

OK. Next idea. Have you actually tried checking for duplicates? You can do that quite easily:
[tt]
SELECT Empno, COUNT(*) FROM TheTable GROUP BY EmpNo HAVING COUNT(*) > 1[/tt]

Then do the same for Cidempl.

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[KarenJB]

Tore,

I ran that and it did find records but there can be multiple records with the same empno. It's the cidempl field that is unique.

 

[KarenJB]

I ran the command again using CIDEMPL and found no duplicates.

 

[Tore Bleken]

If you study the index expression, you will see that if more than one record is deleted with the same empno, the uniqueness is violated. In my mind it's a terrible solution to a stupid idea.

 

[Mike Lewis]

Karen, you say it stopped part way through a recall. Can you identify the record is stopped on?

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Mike Lewis]

you will see that if more than one record is deleted with the same empno, the uniqueness is violated.

Tore, I saw that as well. But she said there were no deleted records in the table.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Mike Lewis]

Then again, if it is possible that the table might contain deleted records in the future, then there will be a violation if there are two deleted records with the same Empno.

And if the table never contains deleted records, why do the test for DELETED() in the index key?

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[KarenJB]

Mike,

It can contain deleted records, I just packed the file to ensure that it didn't. The application has a procedure to pack alsn the recall I browsed the file and could identify the record it stopped at. I tried recalling all but that record and it stopped again.

 

[Tore Bleken]

Karen, I repeat what I just wrote: It's a terrible solution to a stupid idea. Instead of deleting records, they should be marked, and be made available for re-use.

 

[KarenJB]

Tore,

I appreciate the sentiment but out of my control.

 

[KarenJB]

No worries, I can just rebuild that index manually if the need arises. Thanks for all the ideas, keep up the fight!

 

[Mike Lewis]

So, if you can see which record the recall stopped at, wouldn't that tell you which record was causing the violation? You could then check to see if any other records have the same combination of the two fields.

Or, try temporarily changing the values of the two fields in the poblem record to something that you know for sure is unique, then try the indexing again. That won't solve the problem, but at least it will tell you if it is genuinely a uniqueness violation.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[KarenJB]

Yes Mike, I did try that with no luck. This is just as Churchill described: a riddle, wrapped in a mystery, inside an enigma. Thank you anyway.