Change primary key and autoincrement

Question

I've looked through the internet with not so great results.

Here's my table:

+------------------+---------+------+-----+---------+----------------+
| Field            | Type    | Null | Key | Default | Extra          |
+------------------+---------+------+-----+---------+----------------+
| jobid            | int(11) | NO   | PRI | NULL    | auto_increment |
| locid            | int(11) | YES  |     | 0       |                |
| userid           | int(11) | YES  |     | 0       |                |
| createdate       | int(11) | YES  |     | 0       |                |
| jobapplicationid | int(11) | YES  |     | NULL    |                |
+------------------+---------+------+-----+---------+----------------+
5 rows in set (0.00 sec)

I want to change the autoincrementing primary key to jobapplicationid from its original jobid

I've tried alter table jobs_applications drop primary key jobid with no success.

Any help would be greatly appreciated.

Answer 1

-- remove pk
ALTER TABLE `jobs_applications` DROP PRIMARY KEY;
-- remove auto inc
ALTER TABLE `jobs_applications` CHANGE `jobid` `jobid` INT(11) UNSIGNED NOT NULL;
-- set pk and auto inc to the other column
ALTER TABLE `jobs_applications` CHANGE `jobapplicationid` `jobapplicationid` INT(11)AUTO_INCREMENT PRIMARY KEY;