index problem

[diem]

Hello everyone,

For example, I have a table like this and an index with UNIQUE type is set to Make with name MAKE.

ID Make Model
1 AGCO test1
2 AGCO test2
3 AXIAL test3
4 AXIAL test4

If I do a command "SET ORDER TO MAKE", the result is:
ID Make Model
1 AGCO test1
2 AXIAL test3


But if I delete the first record with ID = 1, then I run the command again "SET ORDER TO MAKE", the result is changed to:
ID Make Model
2 AXIAL test3

Should it still have the result like this even though first record ID= 1 is deleted. I need to have the result below. Note: I need to have "SET DELETED ON" in this case:
ID Make Model
2 AGCO test2
3 AXIAL test3

Does anyone know what's wrong with this UNIQUE type of index and why it gave the wrong result if the first record of each Make type is deleted? Any help is appreciated. Thank you

 

[DSummZZZ]

You would have to reindex the file because when the index is created, it only references one of the items if there are more than one. If you delete that record, that item no longer exists, so the index would need to be recreated so it has a new item to point to in place of the deleted one.

Dave S.

 

[chpicker]

I highly recommend against using UNIQUE indexes. Honestly I can't think of a good use for them at all. Instead of setting the index UNIQUE and using SET ORDER TO, consider using a Regular index and issue the following statement:

SELECT ID, make, model FROM myTable ;
    GROUP BY make INTO CURSOR temp

This will give you one record per Make no matter which record gets deleted.

Just my opinion on the UNIQUE indexes, of course. Just because I can't think of a use doesn't mean there isn't one. )

Ian