Firstly, don't cross post unless the question *really* has to do with both (in this
case, it should be just db).
Secondly, your id field should only be used as a reference to a row (not showing order
of record). Auto increments are exactly that, the rdbms will take care of creating the
increment.
eg. you have this table
tblName
ID | field1 | field2
Your insert sql should be something like this:
INSERT INTO tblName VALUES ('','value1', 'value2')
OR
INSERT INTO tblName SET field1 = 'value1', field2 = 'value2'
The reason why the 'hole' is there, is to maintain data integrity. Say you have two
tables and they relate to each other (through the id). If you delete a record from one
and it relates to something in the other table, if you add a new record using the old
id, it will join with the second table, when it shouldn't. Confusing? yeah its just
cause i can't explain it right.
If you want to use numbering for your records, create it dynamically when you display
the data.
Adam
--- Original Message ---
rite,
my primary key column ("id") is set to auto_increment as usual which is very
handy. But when I delete a row, the auto_increment just keeps incrementing
and there's this 'hole' left where I deleted the row!
Apart from this looking ugly, it poses another problem. In my PHP script
where I can add new rows, I query the table, checking how many rows in the
table altogether and set the new id as the next number, but this doesnt work
if theres 'holes' in the id field, as the new record tries to overwrite
another id.
So I've 2 questions
1) Can the next auto_increment value be 'set' by a SQL query????
2) Can I get a SQL query to INSERT INTO the first 'hole' it finds in the ID
column??
TIA
