Uploaded image for project: 'Percona Server for MySQL'
  1. Percona Server for MySQL
  2. PS-1635

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

    Details

    • Type: Bug
    • Status: Done
    • Priority: High
    • Resolution: Fixed
    • Affects Version/s: None
    • Fix Version/s: None
    • Component/s: None

      Description

      **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

        Smart Checklist

          Attachments

            Activity

              People

              • Assignee:
                Unassigned
                Reporter:
                lpjirasync lpjirasync (Inactive)
              • Votes:
                0 Vote for this issue
                Watchers:
                1 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved: