Help with index of a table

[marcelosoy1]

hi guys,

the thing is i am creating a program in vb.net that connects to an informix database and then you can add registers through vb.net to de db

everything works well but the problem starts when I want to delete a register from there i have a button that deletes the selected register from a datagrid and the button deletes it correctly

but the index of the table doesn´t get deleted

for example: I have a table with this 3 fields:

ID | name | last name

I create a register and its id is 1
then i create another one and its id is 2
but after that i delete both registers
and after that i create another register that is supposed to be id = 1 cause
the table is empty again
but it appear as 3 keeping the id count

any help with this?

thanks anyway

[leinad31]

Yes, that's how its supposed to work... do you really need it to go back to 1? What's the basis of this requirement other than aesthetics? Was it imposed by auditor? Why can't you use 3 as the starting value? Did you check if this (reuse of unique ID) will impact your foreign key references?

[marcelosoy1]

i want it that way because in the datagrid i have in the form it shows

ID | Name | Last name (and so when you add register is appears like this)

1 John wayne
2 mark suers
( here you delete both)

here you add a new one to this empty list ad appear like this:

ID | Name | Last name

3 Steven johnson

being this the only register on the list.
i want it always to start from 1 even when you delete all the data and add new one.

And the reference thing there is no problem cause the db contain only 1 table

[leinad31]

It's a unique ID/primary key of a record, it's not a list order number. It's supposed to work that way. You're only opening a can of worms with your requirement. It's not worth the headache. Your time is better spent elsewhere, e.g. improving other areas of your application, making it work.

Rather than making it revert to one, you should be asking whether user requires a natural key (something like a social security number) or a surrogate key (auto-increment number); google the difference. Besides, in a real system with hundreds/thousands of records, users won't give a damn whether first record started at 1 as long as the unique ID fulfills its purpose. Serializing transaction reference numbers matter more in the bigger scheme of things.

[FunkyDexter]

As Leinad has said, the purpose of an index column is to gove a unique number to every record that is ever created. So each number will be used once and once only, regardless of deletions in the system. It's not the tool you're looking for.

Before we can provide a solution you really need to analyse your requirement further. If you create records 1, 2 and 3, then delete record 2, should record 3 now become record 2? If not your records will be 1 and 3 (with a gap), should the next record created be 2 or 4?

If your requirement is purely aestetic (i.e. a count of displayed records) then I would suggest you shouldn't store it in the database. Instead generate the count as you query or as you populate your grid. If your requirement is about identifying individual records within your database then, as Leinad has said, you really don't want to be re-using ids, it will lead to all sorts of conistency errors.

[marcelosoy1]

well thanks for the advices so now i think that what i was looking for is just a variable that list them in order..

And this program i´m making is just a one form vb.net proyect with a db cause i´m practicing my skills with it because in 2 months i will be doing a really big one and so i wanna be the more familiarized posible with it you know

but as i said b4 thanks for your advices...

[leinad31]

Interacting with database is often straightforward... use a recordset (or something comparable), execute directly on connection, or do a parameterized query.

Often the problem is with how said objects are used; excessive locking, lack of concurrency control (these are some of the problems with your revert to 1 scenario), inefficient queries, too large resultsets, etc. Developers are often content on just getting the output with no concern for scalability, maintainability and performance of their queries.

If you have time, it would be worthwhile to read up on SQL tuning for your target database. That way you won't take all database programming samples as the only way of interacting with database as they are often poorly written (performance wise) for the sake of understandability.