删除自增长列主键

在删除自增长列主键时，需要先改变自增长属性，然后才能删除主键。

1. mysql> show create table t1\\G
   
2. *************************** 1. row ***************************
   
3.        Table: t1
   
4. Create Table: CREATE TABLE `t1` (
   
5.   `id` int(11) NOT NULL AUTO_INCREMENT,
   
6.   `name` varchar(50) DEFAULT NULL,
   
7.   PRIMARY KEY (`id`)
   
8. ) ENGINE=InnoDB DEFAULT CHARSET=latin1
   
9. 1 row in set (0.01 sec)

表t1的id列有自增长属性，且为主键

1. mysql> alter table t1 drop primary key;
   
2. ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key

直接删除主键报错，需要先改变表的定义

1. mysql> alter table t1 change id id int(10);
   
2. Query OK, 0 rows affected (0.13 sec)
   
3. Records: 0 Duplicates: 0 Warnings: 0
   
4. 
   
5. mysql> show create table t1\\G
   
6. *************************** 1. row ***************************
   
7.        Table: t1
   
8. Create Table: CREATE TABLE `t1` (
   
9.   `id` int(10) NOT NULL DEFAULT \'0\',
   
10.   `name` varchar(50) DEFAULT NULL,
    
11.   PRIMARY KEY (`id`)
    
12. ) ENGINE=InnoDB DEFAULT CHARSET=latin1
    
13. 1 row in set (0.00 sec)

然后在删除自增长属性列上的主键

1. mysql> alter table t1 drop primary key;
   
2. Query OK, 0 rows affected (0.06 sec)
   
3. Records: 0 Duplicates: 0 Warnings: 0
   
4. 
   
5. mysql> show create table t1\\G
   
6. *************************** 1. row ***************************
   
7.        Table: t1
   
8. Create Table: CREATE TABLE `t1` (
   
9.   `id` int(10) NOT NULL DEFAULT \'0\',
   
10.   `name` varchar(50) DEFAULT NULL
    
11. ) ENGINE=InnoDB DEFAULT CHARSET=latin1
    
12. 1 row in set (0.00 sec)

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/12219480/viewspace-1732531/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/12219480/viewspace-1732531/