Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 506
  • Last Modified:

How to rebuild a primary index

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
andy7789
Asked:
andy7789
  • 4
  • 2
  • 2
  • +2
1 Solution
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
that command only drops the primary key, you have to add it then:

ALTER TABLE mytable ADD PRIMARY KEY ( column_name );
0
 
Raynard7Commented:
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
 
andy7789Author Commented:
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
Transaction-level recovery for Oracle database

Veeam Explore for Oracle delivers low RTOs and RPOs with agentless transaction log backup and transaction-level recovery of Oracle databases. You can restore the database to a precise point in time, even to a specific transaction.

 
andy7789Author Commented:
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
 
VoteyDiscipleCommented:
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
 
andy7789Author Commented:
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
 
VoteyDiscipleCommented:
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
 
SheeriCommented:
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
 
Raynard7Commented:
Have you been able to get th esoloutions working - by changing the php rather than rebuilding the index?
0
 
andy7789Author Commented:
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

Transaction-level recovery for Oracle database

Veeam Explore for Oracle delivers low RTOs and RPOs with agentless transaction log backup and transaction-level recovery of Oracle databases. You can restore the database to a precise point in time, even to a specific transaction.

  • 4
  • 2
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now