fnrbkypj

1-- Let's say you had a table fruit
2
3CREATE TABLE IF NOT EXISTS `fruit` (
4  `id` int(11) NOT NULL auto_increment,
5  `blah` varchar(255) NOT NULL,
6  PRIMARY KEY  (`id`)
7);
8
9-- Then you populate it, and delete some rows:
10-- id, blah
11-- 1, apple
12-- 4, orange
13-- 5, banana
14
15-- Add a new column
16ALTER TABLE  `fruit` ADD  `new_key` INT NOT NULL FIRST;
17
18-- Move over the existing keys into the new key
19UPDATE `fruit` set new_key = id;
20
21-- Make alterations to the new_key by tightening up:
22UPDATE `fruit` set new_key = new_key - 2 where new_key > 1;
23
24-- Now you have:
25-- new_key, id, blah
26-- 1, 1, apple
27-- 2, 4, orange
28-- 3, 5, banana
29
30-- Once you have it like you like it, do:
31ALTER TABLE `fruit` DROP  `id`;
32ALTER TABLE  `fruit` ADD PRIMARY KEY (  `new_key` );
33ALTER TABLE  `fruit` CHANGE  `new_key`  `id` INT( 11 ) NOT NULL AUTO_INCREMENT
34
35-- then:
36-- id, blah
37-- 1, apple
38-- 2, orange
39-- 3, banana