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