将其他字段设置为自增主键

1、创建测试表，查看表结构

Mysql> desc test_autoinc;

+-------+--------------+------+-----+---------+----------------+

| Field | Type         | Null | Key | Default | Extra          |

+-------+--------------+------+-----+---------+----------------+

| id    | int(11)      | NO   | PRI | NULL    | auto_increment |

| c1    | int(11)      | YES  | UNI | NULL    |                |

| c2    | varchar(100) | YES  |     | NULL    |                |

| id_no | int(20)      | NO   |     | NULL    |                |

+-------+--------------+------+-----+---------+----------------+

4 rows in set (0.00 sec)

2、将其他字段设置为自增主键（当前表上已经有自增主键，设置其他字段为自增主键报错）

mysql> 

mysql> alter table test_autoinc modify id_no int(11) auto_increment,add primary key(id_no);

ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key

mysql> 

mysql> desc test_autoinc;

+-------+--------------+------+-----+---------+----------------+

| Field | Type         | Null | Key | Default | Extra          |

+-------+--------------+------+-----+---------+----------------+

| id    | int(11)      | NO   | PRI | NULL    | auto_increment |

| c1    | int(11)      | YES  | UNI | NULL    |                |

| c2    | varchar(100) | YES  |     | NULL    |                |

| id_no | int(20)      | NO   |     | NULL    |                |

+-------+--------------+------+-----+---------+----------------+

4 rows in set (0.00 sec)

3、删除表上的约束

mysql> 

mysql> alter table test_autoinc change id id int;

Query OK, 5 rows affected (0.07 sec)

Records: 5  Duplicates: 0  Warnings: 0

mysql> 

mysql> desc test_autoinc;

+-------+--------------+------+-----+---------+-------+

| Field | Type         | Null | Key | Default | Extra |

+-------+--------------+------+-----+---------+-------+

| id    | int(11)      | NO   | PRI | NULL    |       |

| c1    | int(11)      | YES  | UNI | NULL    |       |

| c2    | varchar(100) | YES  |     | NULL    |       |

| id_no | int(20)      | NO   |     | NULL    |       |

+-------+--------------+------+-----+---------+-------+

4 rows in set (0.00 sec)

5、删除表上的主键

mysql> alter table test_autoinc drop primary key;

Query OK, 5 rows affected (0.07 sec)

Records: 5  Duplicates: 0  Warnings: 0

mysql> 

mysql> desc test_autoinc;

+-------+--------------+------+-----+---------+-------+

| Field | Type         | Null | Key | Default | Extra |

+-------+--------------+------+-----+---------+-------+

| id    | int(11)      | NO   |     | NULL    |       |

| c1    | int(11)      | YES  | UNI | NULL    |       |

| c2    | varchar(100) | YES  |     | NULL    |       |

| id_no | int(20)      | NO   |     | NULL    |       |

+-------+--------------+------+-----+---------+-------+

4 rows in set (0.00 sec)

mysql> show variables like 'sql_mode'; 

+---------------+-------------------------------------------------------------------------------------------------------------------------------------------+

| Variable_name | Value                                                                                                                                     |

+---------------+-------------------------------------------------------------------------------------------------------------------------------------------+

| sql_mode      | ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |

+---------------+-------------------------------------------------------------------------------------------------------------------------------------------+

1 row in set (0.00 sec)

mysql> select * from test_autoinc;

+----+------+-------+-------+

| id | c1   | c2    | id_no |

+----+------+-------+-------+

|  6 |    1 | abc   |     0 |

|  8 |    3 | abcdd |     0 |

|  9 |    4 | abcdd |     0 |

| 10 |    5 | abcdd |     0 |

| 11 |    2 | eeee  |     0 |

+----+------+-------+-------+

5 rows in set (0.00 sec)

mysql> update test_autoinc set id_no=1 ;

Query OK, 5 rows affected (0.14 sec)

Rows matched: 5  Changed: 5  Warnings: 0

mysql> 

mysql> select * from test_autoinc;

+----+------+-------+-------+

| id | c1   | c2    | id_no |

+----+------+-------+-------+

|  6 |    1 | abc   |     1 |

|  8 |    3 | abcdd |     1 |

|  9 |    4 | abcdd |     1 |

| 10 |    5 | abcdd |     1 |

| 11 |    2 | eeee  |     1 |

+----+------+-------+-------+

5 rows in set (0.00 sec)

mysql> alter table test_autoinc add primary key(id_no) ,modify id_no int(11) auto_increment;

ERROR 1062 (23000): ALTER TABLE causes auto_increment resequencing, resulting in duplicate entry '1' for key 'PRIMARY'

mysql> alter table test_autoinc add primary key(id_no);

ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'

mysql> update test_autoinc set id_no=2 where id=7 ;

Query OK, 0 rows affected (0.00 sec)

Rows matched: 0  Changed: 0  Warnings: 0

mysql> update test_autoinc set id_no=3 where id=8 ;

Query OK, 1 row affected (0.01 sec)

Rows matched: 1  Changed: 1  Warnings: 0

mysql> update test_autoinc set id_no=4 where id=9 ;

Query OK, 1 row affected (0.00 sec)

Rows matched: 1  Changed: 1  Warnings: 0

mysql> update test_autoinc set id_no=5 where id=10 ;

Query OK, 1 row affected (0.01 sec)

Rows matched: 1  Changed: 1  Warnings: 0

mysql> update test_autoinc set id_no=6 where id=11 ;

Query OK, 1 row affected (0.00 sec)

Rows matched: 1  Changed: 1  Warnings: 0

mysql> 

mysql> select * from test_autoinc;

+----+------+-------+-------+

| id | c1   | c2    | id_no |

+----+------+-------+-------+

|  6 |    1 | abc   |     1 |

|  8 |    3 | abcdd |     3 |

|  9 |    4 | abcdd |     4 |

| 10 |    5 | abcdd |     5 |

| 11 |    2 | eeee  |     6 |

+----+------+-------+-------+

5 rows in set (0.00 sec)

mysql> 

mysql> alter table test_autoinc add primary key(id_no) ,modify id_no int(11) auto_increment;

Query OK, 5 rows affected (0.27 sec)

Records: 5  Duplicates: 0  Warnings: 0

mysql> 

mysql> show create table test_autoinc\G;

*************************** 1. row ***************************

       Table: test_autoinc

Create Table: CREATE TABLE `test_autoinc` (

  `id` int(11) NOT NULL,

  `c1` int(11) DEFAULT NULL,

  `c2` varchar(100) DEFAULT NULL,

  `id_no` int(11) NOT NULL AUTO_INCREMENT,

  PRIMARY KEY (`id_no`),

  UNIQUE KEY `c1` (`c1`)

) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8

1 row in set (0.00 sec)

ERROR: 

No query specified

mysql>