Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
Renumber the keys of MySQL table
-- Let's say you had a table fruit
CREATE TABLE IF NOT EXISTS `fruit` (
`id` int(11) NOT NULL auto_increment,
`blah` varchar(255) NOT NULL,
PRIMARY KEY (`id`)
);
-- Then you populate it, and delete some rows:
-- id, blah
-- 1, apple
-- 4, orange
-- 5, banana
-- Add a new column
ALTER TABLE `fruit` ADD `new_key` INT NOT NULL FIRST;
-- Move over the existing keys into the new key
UPDATE `fruit` set new_key = id;
-- Make alterations to the new_key by tightening up:
UPDATE `fruit` set new_key = new_key - 2 where new_key > 1;
-- Now you have:
-- new_key, id, blah
-- 1, 1, apple
-- 2, 4, orange
-- 3, 5, banana
-- Once you have it like you like it, do:
ALTER TABLE `fruit` DROP `id`;
ALTER TABLE `fruit` ADD PRIMARY KEY ( `new_key` );
ALTER TABLE `fruit` CHANGE `new_key` `id` INT( 11 ) NOT NULL AUTO_INCREMENT
-- then:
-- id, blah
-- 1, apple
-- 2, orange
-- 3, banana
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.