×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Index Violation - VFP 9.0

Index Violation - VFP 9.0

Index Violation - VFP 9.0

(OP)
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.

RE: Index Violation - VFP 9.0

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

RE: Index Violation - VFP 9.0

(OP)
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.

RE: Index Violation - VFP 9.0

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

RE: Index Violation - VFP 9.0

(OP)
No null values in those fields.

RE: Index Violation - VFP 9.0

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

RE: Index Violation - VFP 9.0

(OP)
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.

RE: Index Violation - VFP 9.0

OK. Next idea. Have you actually tried checking for duplicates? You can do that quite easily:

SELECT Empno, COUNT(*) FROM TheTable GROUP BY EmpNo HAVING COUNT(*) > 1


Then do the same for Cidempl.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

RE: Index Violation - VFP 9.0

(OP)
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.

RE: Index Violation - VFP 9.0

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

RE: Index Violation - VFP 9.0

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.

RE: Index Violation - VFP 9.0

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

RE: Index Violation - VFP 9.0

Quote:

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

RE: Index Violation - VFP 9.0

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

RE: Index Violation - VFP 9.0

(OP)
Mike,

It can contain deleted records, I just packed the file to ensure that it didn't. The application has a procedure to pack also.On 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.

RE: Index Violation - VFP 9.0

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.

RE: Index Violation - VFP 9.0

(OP)
Tore,

I appreciate the sentiment but out of my control.

RE: Index Violation - VFP 9.0

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

RE: Index Violation - VFP 9.0

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

RE: Index Violation - VFP 9.0

(OP)
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.

RE: Index Violation - VFP 9.0

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 Smileing

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.

RE: Index Violation - VFP 9.0

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

RE: Index Violation - VFP 9.0

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

RE: Index Violation - VFP 9.0

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

RE: Index Violation - VFP 9.0

Hi Karen

The usual solution is to:
index on empno tag cempno candidate for not deleted()

That way non-deleted employees are kept unique. Deleted employees are ignored. Recalling one with a duplicate empno will be prevented. You should be able to add code to deal with that by offering a new empno etc.

Your indexes are not useful to optimize queries unless they are coded as:
where EMPNO + IIF(DELETED(),CIDEMPL,SPACE(5)) = 'some emp no'

You would be wise to add another index to help optimize queries on empno:
index on empno tag xempno

That would come into plan in a query such as:
where EMPNO = 'some emp no'

Mike Yearwood - Former FoxPro MVP

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close