Solved

How to rebuild a primary index

Posted on 2006-11-01
10
460 Views
Last Modified: 2008-02-01
Hi

I need to rebuild the primary index with auto increment to remove all the gaps. What is the correct way of doing this?

Is it

ALTER TABLE mytable DROP PRIMARY KEY

Please, advise.

Thanks
-A-
0
Comment
Question by:andy7789
  • 4
  • 2
  • 2
  • +2
10 Comments
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
Comment Utility
that command only drops the primary key, you have to add it then:

ALTER TABLE mytable ADD PRIMARY KEY ( column_name );
0
 
LVL 35

Expert Comment

by:Raynard7
Comment Utility
Hi,

I would not advise doing this - the gaps do not necessarily mean anything and you should never rely on the numbers being indicative of anything (order) - just that they would be unique.

If you wanted to rebuild everything then dropping the index would be insufficient, you would be best dropping the whole field and index and then adding the field and index again.
0
 

Author Comment

by:andy7789
Comment Utility
I use primary id as a sort of counter in the php cycle. Reducing the max number gives me better performance. If Ii drop the promary key what happens with auto increment? Should I also

ALTER TABLE tbl AUTO_INCREMENT = 1

0
 

Author Comment

by:andy7789
Comment Utility
When I am trying to drop the promary key, I am getting an error:

"Incorrect table definition; there can be only one auto column and it must be defined as a key "

What should i do first? remove auto increment?
0
 
LVL 19

Expert Comment

by:VoteyDisciple
Comment Utility
I suspect what you really need is a better technique on the PHP side; relying on contiguous primary key values doesn't seem like a good idea for a variety of reasons.  Databases are pretty good at not caring about that sort of thing, so application code usually should be too.

I'd strongly suggest taking a different approach to this, as others have already.


If you're dead set on it, then the following will work:

ALTER TABLE the_table
  DROP PRIMARY KEY
, DROP COLUMN id
, ADD COLUMN id INT AUTO_INCREMENT PRIMARY KEY FIRST
;

This must be done in a single query, since any one of those actions by itself would be illegal.  You cannot remove the primary key from a table, you cannot add an AUTO_INCREMENT field to a table that already has a key, et cetera.  Doing them all at once, though, will work.
0
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

Author Comment

by:andy7789
Comment Utility
Agreed - this is a weak part of the code. The problem is that I have a html form  - table generated from the database. The user can select any rows by ticking the appropriate checkboxes and submit the whole bunch of their IDs as POST. The only solution i could find is to assign a checkbox name to the row's id, i.e. <input type="checkbox" name="<? echo $row_p['id']; ?>". In brief, I am getting POST[id], where id is just a number.

For example, I may get POST[5], POST[125] and POST[1555]. Since those POSTs are not indexed, I have to cycle through all the numbers to extract just three numbers.

This is the reason, why I am getting the mess with table indexes, because I have to check those ID's across all possible values of the primary index.

If you have some better idea how to do this, I wold be more than happy to get rid of this messy technoique.
0
 
LVL 19

Accepted Solution

by:
VoteyDisciple earned 500 total points
Comment Utility
Ahhh, I see.  Sure, I can give an excellent solution to that.

Use this:

<input type="checkbox" name="row_p[<?= $row_p['id'] ?>]" />


This will generate names like name="row_p[5]"   (and row_p[125] and row_p[1555]).

PHP will, as you might hope, generate an array from this when it creates $_POST, so you'd write:

foreach($_POST['row_p'] as $key => $value) {
   // Here $key is the ID (5, 125, 1555)
   // $value is just the value of the checkbox, so probably 'on' -- you'll probably ignore it completely
}
0
 
LVL 4

Expert Comment

by:Sheeri
Comment Utility
I agree with the above.  Primary keys should NOT be changed frequently.  I suggest adding a column called "php_counter" and let the php program manage that.  

If you insist on misusing auto_increment in the database to contain business logic, though, you would need to drop the auto_increment column and re-add it, as VoteyDisciple  said.
0
 
LVL 35

Expert Comment

by:Raynard7
Comment Utility
Have you been able to get th esoloutions working - by changing the php rather than rebuilding the index?
0
 

Author Comment

by:andy7789
Comment Utility
Thanks a lot! I have got the VoteyDisciple' solution working with rebuilding another array as:

$sel_array = array();
if(isset($_POST['sel'])) {
    $i=0;
foreach($_POST['sel'] as $key=>$value) {
    $sel_array[$i] = $key;
    $i++;
}
}

In fact, I do not need even $value, as it is "Yes" for all the elements of the POST, because the inputs are checkboxes.
0

Featured Post

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

Foreword This is an old article.  Instead of using the MySQL extension that was used in the original code examples, please choose one of the currently supported database extensions instead.  More information is available here: MySQLi / PDO (http://…
I have been using r1soft Continuous Data Protection (http://www.r1soft.com/linux-cdp/) for many years now with the mySQL Addon and wanted to share a trick I have used several times. For those of us that don't have the luxury of using all transact…
Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…

743 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now