Bug #33885 Issue warning when removing AUTO_INCREMENT option from column

• Submitted: 16 Jan 2008 11:06
• Reporter: Geert Vanderkelen
• Status: Verified
• Category: MySQL Server: DDL
• Severity: S4 (Feature request)
• Version: 5.1
• OS: Any
• CPU Architecture: Any

[16 Jan 2008 11:06] Geert Vanderkelen

Description:
It would be great that when altering table's primary key having the auto_increment option a warning would be given when one forgets to set the option back.

For example:
  CREATE TABLE `t1` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `v` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`id`)
  );

If we want to make the `id` column BIGINT, we could do this:
  mysql> ALTER TABLE `t1` MODIFY `id` BIGINT NOT NULL;

Resulting in:
 CREATE TABLE `t1myisam` (
  `id` bigint(20) NOT NULL,
  `v` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`id`)
 );

Oops, the AUTO_INCREMENT is gone. Of course, we should have added it to the ALTER TABLE statement, and correct would have been:
 mysql> ALTER TABLE `t1` MODIFY `id` BIGINT NOT NULL AUTO_INCREMENT;

It would be nice having at least a warning when one does incorrectly.
A warning saying for example: "AUTO_INCREMENT option was removed from Primary Key.", or something like that.
One can argue that warnings can be given for the other options, but I think the AUTO_INCREMENT might have direct impact with errors in applications.

How to repeat:
See Description.