MySQL – How to Change Table Auto Increment Primary Key

MySQL

Now i have a table with Auto_increment id as Pri key, and exchange_id as key, How could I change the primary key to field [ticker]..?

table

Best Answer

The auto_increment field must be an index, or be the first column of one. If you try to just drop the primary key, MySQL will complain. You have to delete the auto_increment property before moving it:

mysql> ALTER TABLE mytable MODIFY id int, DROP PRIMARY KEY, ADD PRIMARY KEY (ticker);
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc mytable;
+-------------------+--------------+------+-----+---------+-------+
| Field             | Type         | Null | Key | Default | Extra |
+-------------------+--------------+------+-----+---------+-------+
| id                | int(11)      | YES  |     | NULL    |       |
| exchange_id       | int(11)      | YES  | MUL | NULL    |       |
| ticker            | varchar(32)  | NO   | PRI | NULL    |       |
| instrument        | varchar(64)  | NO   |     | NULL    |       |
| name              | varchar(255) | YES  |     | NULL    |       |
| sector            | varchar(255) | YES  |     | NULL    |       |
| currency          | varchar(32)  | YES  |     | NULL    |       |
| created_date      | datetime     | NO   |     | NULL    |       |
| last_updated_date | datetime     | NO   |     | NULL    |       |
+-------------------+--------------+------+-----+---------+-------+
9 rows in set (0.00 sec)

If you want id to continue being auto_increment, you have to do also:

mysql> ALTER TABLE mytable ADD INDEX `id` (id), MODIFY id int auto_increment;
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc mytable;
+-------------------+--------------+------+-----+---------+----------------+
| Field             | Type         | Null | Key | Default | Extra          |
+-------------------+--------------+------+-----+---------+----------------+
| id                | int(11)      | NO   | MUL | NULL    | auto_increment |
| exchange_id       | int(11)      | YES  | MUL | NULL    |                |
| ticker            | varchar(32)  | NO   | PRI | NULL    |                |
| instrument        | varchar(64)  | NO   |     | NULL    |                |
| name              | varchar(255) | YES  |     | NULL    |                |
| sector            | varchar(255) | YES  |     | NULL    |                |
| currency          | varchar(32)  | YES  |     | NULL    |                |
| created_date      | datetime     | NO   |     | NULL    |                |
| last_updated_date | datetime     | NO   |     | NULL    |                |
+-------------------+--------------+------+-----+---------+----------------+
9 rows in set (0.00 sec)

Please make sure to create if with the right index (multiple, unique) and nullability (null, not null).

Please also note that, depending on the engine used and/or the queries to be performed, a character field for a primary key may be suboptimal.

Related Question