473,385 Members | 1,343 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,385 software developers and data experts.

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 2022
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

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

Similar topics

15
by: Jack | last post by:
I have a text file of data in a file (add2db.txt) where the entries are already entered on separate lines in the following form: INSERT INTO `reviews` VALUES("", "Tony's", "Lunch", "Great...
0
by: Didier ROS | last post by:
Hi, I am a newbie I want to create a temporary table and I get the following error message : mysql> CREATE TEMPORARY TABLE tempemp AS SELECT * FROM emp; ERROR 1044: Access denied for user:...
2
by: David Heller | last post by:
I have this schema I exported from a database running mysql version 4.01 I'm trying to create this table on a server running mysql 3.23 I keep getting a syntax error near line 11 (I added the line...
7
by: Aleem | last post by:
I need help in writing a stored procedure on SQL Server 2000. Basically the stored procedure's primary task is to generate invoice records and insert the records in a invoice table. In order to...
14
by: bolidev | last post by:
I'm new to SQL and can't figure out how to update my table (StoreItemStatus) that contains the current status for items in each store (STORE_KEY, ITEM_KEY, STATUS,...). I get updated status info...
0
by: Preston Landers | last post by:
Hello all. I am trying to write a query that "just" switches some data around so it is shown in a slightly different format. I am already able to do what I want in Oracle 8i, but I am having...
4
by: Baoqiu Cui | last post by:
Hi, I was playing with MySQL (4.1.9) during the weekend, but noticed a minor problem and would like someone to explain this to me. Basically I could successfully create a table with one unique...
3
by: J West | last post by:
Warning: Error while executing this query:CREATE TABLE "purchaseorder" ( "PurchaseOrderID" int(10) unsigned NOT NULL auto_increment, "PurchaseCost" double unsigned zerofill NOT NULL default...
48
by: phillip.s.powell | last post by:
MySQL 3.23.58 - 4.0.17 (yep, several database server instances, don't ask) I have database Spring with table Students I have database Summer with table Students I am tasked to produce a...
15
by: Cheryl Langdon | last post by:
Hello everyone, This is my first attempt at getting help in this manner. Please forgive me if this is an inappropriate request. I suddenly find myself in urgent need of instruction on how to...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...
0
by: ryjfgjl | last post by:
In our work, we often need to import Excel data into databases (such as MySQL, SQL Server, Oracle) for data analysis and processing. Usually, we use database tools like Navicat or the Excel import...
0
by: taylorcarr | last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
0
by: Charles Arthur | last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
0
by: aa123db | last post by:
Variable and constants Use var or let for variables and const fror constants. Var foo ='bar'; Let foo ='bar';const baz ='bar'; Functions function $name$ ($parameters$) { } ...
0
by: emmanuelkatto | last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud. Please let me know. Thanks! Emmanuel
0
BarryA
by: BarryA | last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
1
by: nemocccc | last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
0
by: Hystou | last post by:
There are some requirements for setting up RAID: 1. The motherboard and BIOS support RAID configuration. 2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.