UNIQUENESS OF INDEX ID IS VIOLATED 1

[spysab]

guyz please help!.

I followed a tutorial in youtube. i followed everything. works perfectly aside from table ID index. In all my tables, if i set its index as ascending (column "ID ") and INDEXES type is CANDIDATE as per youtube tutorial, this is the error whenever im adding new entry.

"UNIQUENESS OF INDEX ID IS VIOLATED"

so trying to find whats wrong by myself, i remove ascending and make is as none. then it works fine. but the table indexes are all zero "0" and another form that is bounded to the company name that requires its index is not working.

i hope i explained it right. but basically, how do i fix this? in youtube, same setup, same code, same name, same example,. works fine.
i cant figure it out.

thanks guyz.

 

[spysab]

Actually i know whats the problem but i dont know how to fix.
Index should have a series of number like 1,2,3,4,5 and so on. every entry.
but mine stays in 1 number which is zero.

 

[Scott24x7]

spysab,
What field is being indexed?
If it is an interger field, does it use the Auto-increment value?

Also, what buffering on the table/record are you using?


Best Regards,
Scott
MSc ISM, MIET, MASHRAE, CDCP, CDCS, CDCE, CTDC, CTIA, ATS

"I try to be nice, but sometimes my mouth doesn't cooperate.

 

[spysab]

Hi Scott24x7,

here, see screenshot. thats the table and yes it is an integer field.

"If it is an interger field, does it use the Auto-increment value?"
- sounds like you solve this here.. how to put auto increment value?
i see autoincrement field but it is grayed out.


"Also, what buffering on the table/record are you using?"

- is this what can be seen on properties? it says " Buffermode - 0-none(default)

 

[Scott24x7]

spysab,
If you open the table in the Table designer, and go to the field that is the integer field with the index on it, under TYPE, click the drop down, and right below Integer you will see another type Integer (AutoInc).
Change it to that one, and set the "Next Value" and Step values if you want them to be something other than the current value (sounds like that is 1 in your case).
Or you can leave it alone. Either way, it should fix the issue.

Also, if you're indexing on an integer field, you should use the BINTOC() function for the index. So if your field is called "MyID" in the Index expression you'd put BINTOC(MyID) on that field. I'd also then suggest to make it either Primary or Candidate key.


Best Regards,
Scott
MSc ISM, MIET, MASHRAE, CDCP, CDCS, CDCE, CTDC, CTIA, ATS

"I try to be nice, but sometimes my mouth doesn't cooperate.

 

[spysab]

Scott24x7 !!!!!!!!!


I LOVE YOU!!!!!!!!!!

THANKS WORKS AS EXPECTED!!!!!!

THANKS THANKS THANKS.

 

[Mike Lewis]

I LOVE YOU!!!!!!!!!!

I'm sure Scott will appreciate your gratitude.

But there is a more "official" method of expressing that same emotion. Click the "Great post" button in the bottom right corner of Scott's post. That will place a red star against the post, indicating to the world that a solution has been found.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[spysab]

sure.!

done!

thank you sir.

 

[Olaf Doschke]

What version of FoxPro are you using? And can you point out which video tutorial you watched?

Candidate index is a type legacy versions have and you can still use in all table types. We also do have a primary index type in tables belonging to a DBC (not in free tables) since I guess VFP4 and still, you can use candidate indexes for as many secondary indexes as you like or need to also establish uniqueness in non-key columns, too. For example, in a category table where you'd want an ID number (just to have a primary key as in every table) and a candidate index on category names, so not just the IDS but also the categories are unique. In free tables, you only have candidate indexes.

But you only have automatic sequence numbering since VFP8 introduced the special autoinc integer type. That indeed also works in free tables.

Your case I guess is a normal integer field with candidate index. Using that you have to insert a unique number yourself. You can only APPEND BLANK or add a row in a BROWSE with CTRL+Y when you have no record with ID=0 and when you don't you have it in the new record and need to change the 0 to some number not present so far, which also means you can't bulk insert rows, if they don't already have IDs not yet existing in the table.

So this is just the classical case of stepping on your own foot. Because free tables have no default values you only have 0 in new records unless you use INSERT INTO and specify a value or unless you use integer autoinc instead of integer.

Bye, Olaf.

Olaf Doschke Software Engineering

https://www.doschke.name

 

[Olaf Doschke]

Scott, why use BINTOC() on int fields to index? That's only applicable to cases you want to have compound indexes on two integer fields. Bintoc(ID)+BINTOC(otherint) because otherwise + just sums the two values and 1,2 has the same index key value as 2,1, because 1+2=2+1=3, BINTOC() converts that to a binary string to which + then means concatenation.

But a single int and indeed any single field index can be done on that field alone. The only types you can't index directly are fields longer than 240 bytes or potentially longer than that, i.e. Memos, Blobs, etc. Otherwise, if you want to index a single field, do so, use just the field name as expression.

Bye, Olaf.

Olaf Doschke Software Engineering

https://www.doschke.name

 

[Scott24x7]

Hi Olaf,
Yes, that's true, I guess I've just gotten used to using it with INT fields, as I generally have a second field to add to it, but good point.

spysab,
Thanks for the star and the love.
Cheers,


Best Regards,
Scott
MSc ISM, MIET, MASHRAE, CDCP, CDCS, CDCE, CTDC, CTIA, ATS

"I try to be nice, but sometimes my mouth doesn't cooperate.

 

[Olaf Doschke]

Now that I know the original youtube video series. There's a lot to say and at the same time not to say, eg about primary vs candidate index types. Everyone has their preferences. Nitpicking is unfair in comparison to creating such a whole series of long videos.

Bye, Olaf.

Olaf Doschke Software Engineering

https://www.doschke.name

 

[Olaf Doschke]

Scott, you know that to optimize queries you then also need to write BONTOC(id) = BINTOC(otherid), really a simple index is better to not convolute your query with expressions nobody will know why to use in the SQL. It's better to just have a simple index on IDs.

Bye, Olaf.

Olaf Doschke Software Engineering

https://www.doschke.name

 

[Scott24x7]

Fair enough. I may revisit my thinking and implementation on this.


Best Regards,
Scott
MSc ISM, MIET, MASHRAE, CDCP, CDCS, CDCE, CTDC, CTIA, ATS

"I try to be nice, but sometimes my mouth doesn't cooperate.