about delete from table

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
bugthefixer
Forum Contributor
Posts: 118
Joined: Mon Mar 22, 2004 2:35 am

about delete from table

Post by bugthefixer »

i have a a table which has seq_id as auto increment. when i use delete from table to delete records from the end of table then seq id remains over the same value for example if i have 14 records and i have deleted last two records i.e. 13 and 14 then next time if i insert record seq id should start from 13 but my problem is it starts from 15.
User avatar
CoderGoblin
DevNet Resident
Posts: 1425
Joined: Tue Mar 16, 2004 10:03 am
Location: Aachen, Germany

Post by CoderGoblin »

What you are experiencing is correct. It needs to work that way. Id's may be joined from other,multiple tables. Unless you use constraints deleting a row will not delete the joined items. Adding a new identical id would therefore cause data errors.

View the serial id only as an internal pointer, not something you should use other than informational purposes. If you need another item_no column add it and calculate it when inserting the new row based on a count of the table rows.
User avatar
JAM
DevNet Resident
Posts: 2101
Joined: Fri Aug 08, 2003 6:53 pm
Location: Sweden
Contact:

Post by JAM »

CoderGoblin allready mentioned this; It's supposed to be a pointer to a specific row in the table, not a mean to count or otherwise structure your data.

Not saying that this is your issue, but I want it mentioned yet again for other readers; alot of people use this value to count the records...

Code: Select all

select max(id) from table
...instead of using mysql internal functions for it

Code: Select all

select count(id) from table
Can you describe the issues you are having because of the auto_incr. behaviour?
Post Reply