Question: The developer before me created a database table called "Tools", it contains two fields, toolid - varchar(255) and toolname - varchar(100).

The toolid field is of type varchar, it's the primary key. There is data in the table. 

I would like to convert the varchar toolid field to be of type int, while perserving data w/ their keys, also I want this field to be auto increment. 

Currently the keys are numeric, such as 12 or 22 or 3. 

Whats the best way to do this?

Answer: 
If you want to just change the varchar field to an int field, of course it won't let you, because of the data-type mismatch, however, a very simple fix is:

Create a second table, which will contain all the fields from your primary.

```
CREATE TABLE `Tools2` SELECT * FROM `Tools`;

```

Empty out the data, as we need to change the toolname from varchar to int and can't do this when it's full.

```
TRUNCATE TABLE `Tools2`;

```

Okay, let's change the field type.

```
ALTER TABLE `Tools2` CHANGE `toolname` `toolname` INT(11) NOT NULL;

```

Now let's re-insert the data from the original table.

```
INSERT INTO `Tools2` SELECT * FROM `Tools`;

```

Tools2 is now a correct version of Tools, so remove Tools.

```
DROP TABLE `Tools`;

```

Finally, rename Tools2 to Tools so you're good to go.

```
RENAME TABLE `Tools2` TO `Tools`;

```
Try these three separate commands:

```
ALTER TABLE `Tools` CHANGE `toolid` `toolid` INT( 11 ) NOT NULL;
ALTER TABLE `Tools` ADD PRIMARY KEY ( `toolid` );
ALTER TABLE `Tools` CHANGE  `toolid`  `toolid` INT( 11 ) NOT NULL AUTO_INCREMENT;

```

If you need to adjust the `AUTO_INCREMENT` value:

```
ALTER TABLE `Tools` AUTO_INCREMENT = 10

```