Set other fields to self-increment primary key
1. Create test table and view table structure
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. Set other fields as self-increasing primary keys (there are already self-increasing primary keys in the current table, and setting other fields as self-increasing primary keys results in an error)
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. Delete constraints on the table
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. Delete the primary key on the table
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>
