Need help with create table syntax

[abctech]

Hi all

My frontend is Java and backend is MySql.

The tables that I create have auto incrementing primary keys.

This is the syntax of my create statement :-

Create table ABC(ID Int not null auto_increment primary key,SubjectID Int not null,Level Varchar(20) not null,Duration Int not null,index(SubjectID),foreign key(SubjectID) references Subjects(SubjectID));

My problem is whenever I delete a record the next record that is inserted does have the correct autoincremented ID but it gets inserted in place of the previously deleted record.

For eg I have 10 records in a table each having ID as 1,2,3..so on in a sequential order and now I delete record no 3 and then insert a new record(Id: 11) then its inserted in place of 3..so it now shows 1,2,11,4,5,...10

How can I avoid this?
Thanks in advance.

[ronverdonk]

You cannot avoid this. MySQL nowhere gives any guarantee where its db, tables or data are physically placed. If you want rows to be retrieved/displayed in a particular order, you user the ORDER BY attribute.

Ronald :cool:

[abctech]

You cannot avoid this. MySQL nowhere gives any guarantee where its db, tables or data are physically placed. If you want rows to be retrieved/displayed in a particular order, you user the ORDER BY attribute.

Ronald :cool:

Thank you very much, I used the 'group by' clause and now the records appear in a sequential order in the front-end table.

Ther's another related question if I may,
from the front-end the user is given the facility to delete the records by clicking a 'Delete' button,but even if all the records are deleted and the table becomes empty the next record that is added is alloted the the last inserted row's ID + 1.
That is if the last inserted record had ID: 17 and even after deleting all records from the table I need that the next record added has ID starting again from 1 and not 18. Is this possible?

[ronverdonk]

Not automatically. Let me explain how it works, example:

You have a table with auto_increment column 'id' and values:
1
2
3
After deleting row with id 2, table will be:
1
3
After inserting a new row, the table will be:
1
3
4
You delete all rows from the table and insert a new one, the table will be:
5

If you want to resequence an auto_increment column, you have to do that by dropping the column and adding it again. Like the following way, assuming that your auto_increment column is named 'id':

ALTER TABLE tablename DROP id;
ALTER TABLE tablename
  ADD id INT UNSIGED NOT NULL AUTO_INCREMENT FIRST,
  ADD PRIMARY KEY (id);

Ronald :cool:

[abctech]

Not automatically. Let me explain how it works, example:

You have a table with auto_increment column 'id' and values:
1
2
3
After deleting row with id 2, table will be:
1
3
After inserting a new row, the table will be:
1
3
4
You delete all rows from the table and insert a new one, the table will be:
5

If you want to resequence an auto_increment column, you have to do that by dropping the column and adding it again. Like the following way, assuming that your auto_increment column is named 'id':

ALTER TABLE tablename DROP id;
ALTER TABLE tablename
  ADD id INT UNSIGED NOT NULL AUTO_INCREMENT FIRST,
  ADD PRIMARY KEY (id);

Ronald :cool:

Thanks a lot once again.I really appreciate your help !

I incorporated the above code in my Java program where I first check the max(ID) for my table, if it comes out to be < 1 then I fire the above queries and thus all the newly added records are numbered from 1 again.

My doubt is resolved now!

P.S : By mistake in my last reply I said I used 'group by' instead of 'order by', but its actually 'order by'.

[ronverdonk]

You are welcome.

Ronald :cool: