APORTE: Reorganizar clave primaria auto_increment

[NSD]

Bueno, recientemente me tope con un problema (no muy complejo) pero que no encontre por ningun lado la solucion, y como creo que es algo que a todos al menos una vez le ha pasado, posteo como lo sulucione, seguramente hay otras formas, esta es la que se me ocurrio y funciono.

Problema: Tengo una tabla con datos, con un id autoincrementado con varios cientos de miles de registros, para un proyecto en particular solo nesesito una fraccion de estos, asi que copio la tabla y borro los que no se van a usar asi no ocupan lugar, entonces, quedan enormes bucotes en los id. como es para un proyecto nuevo no puedo dejarlo asi, seria muy precario, no obstante esta tabla se relaciona con otras(con caracteristicas similares) por lo que la reorganizacion tiene que mantener la integridad.

Solucion:
El ejemplo se basa en una tabla de provincias, que tiene vinculadas localidades mediante claves foraneas.

0-> Antes que nada, asegurarse que todas las tablas que dependan de la que se va a reorganizar, (en ente caso la de localidades) tengan un comportamiento CASCADE para las actualizaciones.

Código MySQL:

ALTER TABLE  `localidades`ADD FOREIGN KEY (  `id_provincia` ) REFERENCES  `bd`.`provincias` ( `id_provincia` ) ON DELETE CASCADE ON UPDATE CASCADE ;
 

1-> Crear un indice redundante sobre la clave primaria de la tabla a reorganizar. (Para "engañar" a las claves foraneas)

Código MySQL:

ALTER TABLE  `provincias` ADD INDEX (  `id_provincia` );
 

2-> Borrar el indice PRIMARY de la tabla.

Código MySQL:

ALTER TABLE provincias DROP PRIMARY KEY;
 

3-> Quitar el AUTO_INCREMENT de la ex clave primaria

Código MySQL:

ALTER TABLE  `provincias` CHANGE  `id_provincia`  `id_provincia` SMALLINT( 5 ) UNSIGNED NOT NULL;
 

4-> Crear un campo auxiliar, con clave primaria y AUTO_INCREMENT

Código MySQL:

ALTER TABLE  `provincias` ADD  `id_aux` SMALLINT NOT NULL AUTO_INCREMENT PRIMARY KEY AFTER  `id_provincia`;
 

5-> Actualizar los id originales.

Código MySQL:

UPDATE `provincias` SET `id_provincia` = `id_aux`;
 

6-> Borrar el campo auxiliar.

Código MySQL:

ALTER TABLE `provincias` DROP `id_aux`;
 

7-> Volver a agregar la clave primaria donde corresponde.

Código MySQL:

ALTER TABLE  `provincias` ADD PRIMARY KEY (  `id_provincia` );
 

8-> Agregar el autoincrementado.

Código MySQL:

ALTER TABLE  `provincias` CHANGE  `id_provincia`  `id_provincia` SMALLINT( 5 ) UNSIGNED NOT NULL AUTO_INCREMENT;
 

9-> Borrar el indice redundante

Código MySQL:

ALTER TABLE provincias DROP INDEX id_provincia;
 

10-> Si fuese el caso, se deveria devolver a las tablas dependientes el comportamiento a sus claves foraneas, cambiando el CASCADE por el que corresponda.

[marlanga]

Esto no es una crítica, es una reflexión.

¿Merece la pena el cambio? ¿Muy precario? Yo creo que no. El id autoincremental debe ser un campo interno y oculto para gestionar la tabla, y no lo suelo mostrar nunca. Y aunque se muestre en algún listado, ¿qué mas da que salte del 1.534 al 456.322?
Lo veo como un cambio puramente estético.

[gnzsoloyo]

Este es un tema que se ha tratado muchas veces, y realmente es como dice @marlanga: Es puramente estético.

Sobre este tema hay dos posts en las FAQs qu eno sé si leíste:

http://www.forosdelweb.com/f86/faqs-...1/#post2802568
http://www.forosdelweb.com/f86/faqs-...1/#post3364616

El tema es sencillo, y el método logrado por ti es interesante, pero si necesitabas "trasvasar" esos registros, renumerándolos, yo te habría aconsejado que nos preguntaras antes, porque hay modos mas sencillos de lograrlo, sin tantos pasos.

De todos modos, como experiencia es didáctica.

[NSD]

@marlanga coincido en que la principal finalidad es estética, pero lo de "precario" lo puse porque me parece que si se arranca un proyecto de 0 no queda bien que halla bucotes en los id (amen de que no afecte el funcionamiento).

@gnzsoloyo todas las soluciones y post que vi en internet son similares a esos dos post tuyos que mencionas, no obstante no encontre ninguno que reorganize la clave primaria fisicamente en la base de datos, por eso publique este metodo, no es mas que otra alternativa y cada quien desidira cual le parece mas viable.

¿Que es preferible, tirar 9 lineas sql una única vez, o utilizar una variable contadora en cada select que necesite mostrar el id? yo opto por la primer opcion