469,919 Members | 2,109 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,919 developers. It's quick & easy.

Need help with create table syntax

157 100+
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 :-

Expand|Select|Wrap|Line Numbers
  1. 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.
Feb 12 '07 #1
5 1924
ronverdonk
4,258 Expert 4TB
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:
Feb 12 '07 #2
abctech
157 100+
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?
Feb 13 '07 #3
ronverdonk
4,258 Expert 4TB
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':
Expand|Select|Wrap|Line Numbers
  1. ALTER TABLE tablename DROP id;
  2. ALTER TABLE tablename
  3.   ADD id INT UNSIGED NOT NULL AUTO_INCREMENT FIRST,
  4.   ADD PRIMARY KEY (id);
Ronald :cool:
Feb 13 '07 #4
abctech
157 100+
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':
Expand|Select|Wrap|Line Numbers
  1. ALTER TABLE tablename DROP id;
  2. ALTER TABLE tablename
  3.   ADD id INT UNSIGED NOT NULL AUTO_INCREMENT FIRST,
  4.   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'.
Feb 13 '07 #5
ronverdonk
4,258 Expert 4TB
You are welcome.

Ronald :cool:
Feb 13 '07 #6

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

reply views Thread by Didier ROS | last post: by
2 posts views Thread by David Heller | last post: by
14 posts views Thread by bolidev | last post: by
reply views Thread by Preston Landers | last post: by
4 posts views Thread by Baoqiu Cui | last post: by
3 posts views Thread by J West | last post: by
reply views Thread by Salome Sato | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.