is it a problem to an primary key in mysql table that is int(100) and auto_increament

Posted on 2011-09-08
Medium Priority
Last Modified: 2012-05-12
I created a table that the primary_key is an id that the type is int(100) and AUTO_INCREMENT.
so I notice that even after deleting rows the "id" is still counting from a the former number and doesnt update it. can it cause problems? is there any way I can change that after the table is already have existing rows?

Thank you
Question by:Nura111
LVL 17

Accepted Solution

nanharbison earned 2000 total points
ID: 36507036
Why would you want to mess with the auto-incremented id? You can always get a count of how many rows are in your database.

Expert Comment

ID: 36507050
Auto increment will always increment by +1 from the last inserted row. It wont cause any problems if you are deleting any rows from in between. If you want to reset the auto increment

ALTER TABLE "table_name" auto_increment=1


Author Comment

ID: 36507056
I just don't have a lot of experience in mysql and I thught its might cause problems because I notices I dont have it any other tables in my application.
adn when I first update the table (from the php script) their was a problem there that it wasnt update unless I refreshed the page (every refresh made one update in the table even though it was suppose to update the all table in a foreach loop)  .
I thought that this might be the reason.
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

LVL 17

Assisted Solution

nanharbison earned 2000 total points
ID: 36507075
No, there must be an error in your code if it only makes one update instead of all the rows in the table. Why don;t you post the code in the for loop?

Author Comment

ID: 36507414
PranjalShah: why would I do that Isn't it will cause that there will be the the same primary key more than once?

Author Comment

ID: 36507456
Yes thank you
the next code is update into table and is inside of a while loop that go over 468 rows

the bug is 95% there after debugging the $_SESSION['displayMetro'] result are fine and the results in the table are fine.

but its doing that one update at a time something in there is throwing it out and will do it again only after refresh the page(im guessing that it getting information again from the server..)

//	Insert the table dispaly metro

				$countIt = 0;

		    	foreach ($_SESSION['displayMetro'] as $m) {


				if ($countIt == 1 ){$m['prime_metro'] = 1; $prime_city = $m['metro_name'];}

					if ($countIt == 4) { break; }	

					$insert_query  = "INSERT INTO displayMetro(domain_id, metro_name, population, distance, prime_metro)";

				   $insert_query .= "VALUES('{$m['domain_id']}', '{$m['metro_name']}', '{$m['population']}', '{$m['distance']}', '{$m['prime_metro']}')";


				if (($result=$sql->Query($insert_query,$_SERVER['PHP_SELF'])) === FALSE)

				die(ShowError("Server Query Error"));


Open in new window

LVL 14

Expert Comment

by:Scott Madeira
ID: 36509768
To answer your origianl question: your primary key field is fine.  No need to do anything with it.  It will do its job.

The SQL that you posted is not updating rows.  It is creating new rows.  The first thing you want to check is to make sure your session variable 'DisplayMetro' is a multi-dimensional array that contains all of the rows you are interested in.  

If that variable only has one row of data in it then the loop will only execute one time.


Author Comment

ID: 36512083
yes sorry thats what I meant I want to Insert new rows.      the session varaibles are fine this all code is in a while loop (I didn't added it because its doing other things that are not relevant and just  comlicated the code) but the while loop is going on 460 rows and creating the SESSION for each row and do the foreach for each row.
when comment the Insert query the while is working just fine on the 468 rows at once. (and taking a long time because of a certain action that happen before this code) but when Im updating the db its somehow stop it after every one iterate of the while and will continue only when refresh the page.

again the Session is ok and the result in the db is ok exepect that it happen one at time.

any thought?

Featured Post

The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

What we learned in Webroot's webinar on multi-vector protection.
Article by: Tammy
MySQLTuner is a script written in Perl that allows you to review a MySQL installation quickly and make adjustments to increase performance and stability. The current configuration variables and status data is retrieved and presented in a brief forma…
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …

597 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question