1# 2# Test for bug http://bugs.mysql.com/bug.php?id=77591 3# (ALTER TABLE does not allow to change NULL/NOT NULL if foreign key exists) 4# 5--source include/have_innodb.inc 6 7CREATE TABLE t1(x VARCHAR(36) DEFAULT NULL, UNIQUE(x)) ENGINE=InnoDB; 8 9CREATE TABLE t2(y VARCHAR(36) DEFAULT NULL, 10 FOREIGN KEY(y) REFERENCES t1(x)) ENGINE=InnoDB; 11 12INSERT INTO t1 VALUES ("foo"), (NULL), ("bar"); 13INSERT INTO t2 VALUES ("foo"), (NULL), ("bar"); 14 15SELECT * FROM t1 ORDER BY x ASC; 16SELECT * FROM t2 ORDER BY y ASC; 17 18# Remove STRICT_TRANS_TABLES from sql_mode 19SELECT @@session.sql_mode INTO @old_sql_mode; 20SET SESSION sql_mode='NO_AUTO_CREATE_USER'; 21 22# With the bug present this fails with ER_FK_COLUMN_CANNOT_CHANGE_CHILD 23ALTER TABLE t1 CHANGE COLUMN x x VARCHAR(36) NOT NULL; 24 25SELECT * FROM t1; 26SELECT * FROM t2; 27 28DROP TABLE t2, t1; 29 30# Check that we still do not allow NULL -> NOT NULL transition for FK columns 31CREATE TABLE t1 (x INT PRIMARY KEY) ENGINE=InnoDB; 32 33CREATE TABLE t2 (y INT PRIMARY KEY, z INT DEFAULT NULL, 34 FOREIGN KEY(z) REFERENCES t1(x)) ENGINE=InnoDB; 35 36INSERT INTO t1 VALUES (0); 37 38INSERT INTO t2 VALUES (0, NULL); 39 40--error ER_FK_COLUMN_CANNOT_CHANGE 41ALTER TABLE t2 CHANGE COLUMN z z INT NOT NULL; 42 43SET SESSION sql_mode=@old_sql_mode; 44 45DROP TABLE t2, t1; 46