mysql - 修改主键和自增

Question

我在互联网上查了一下，结果不太好。

这是我的表格:

+------------------+---------+------+-----+---------+----------------+
| 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)

我想将自增主键从原来的jobid更改为jobapplicationid

我尝试过 alter table jobs_applications drop Primary Key jobid 但没有成功。

任何帮助将不胜感激。

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;