LP #1470677: ALTER TABLE does not allow to change NULL/NOT NULL if foreign key exists

**Reported in Launchpad by Sveta Smirnova last update 24-07-2015 06:07:24

ALTER TABLE does not allow to change null-ability of the column if foreign key exists.

Bug is not repeatable with version 5.5.

How to repeat:

mysql> create table instances(
-> uuid varchar(36) default null,
-> unique(uuid)) engine=innodb;
Query OK, 0 rows affected (0.22 sec)

mysql> create table block_device_mapping(
-> instance_uuid varchar(36) DEFAULT NULL,
-> foreign key(instance_uuid) references instances(uuid)) engine=innodb;
Query OK, 0 rows affected (0.20 sec)

mysql> ALTER TABLE instances CHANGE COLUMN uuid uuid VARCHAR(36) NOT NULL;
ERROR 1833 (HY000): Cannot change column 'uuid': used in a foreign key constraint 'block_device_mapping_ibfk_1' of table 'test.block_device_mapping'

Suggested fix:
Allow such changes which does not affect existing data.

PostgreSQL allows this:

postgres=# create table instances(
postgres(# uuid varchar(36) default null,
postgres(# unique(uuid));
CREATE TABLE

postgres=# create table block_device_mapping(
postgres(# instance_uuid varchar(36) DEFAULT NULL,
postgres(# foreign key(instance_uuid) references instances(uuid));
CREATE TABLE

postgres=# ALTER TABLE instances ALTER COLUMN uuid SET NOT NULL;
ALTER TABLE