Debian Codegraph @

mysql-test/suite/innodb/t/innodb_bug21704.test

raw
1 --echo #
2 --echo # Bug#21704: Renaming column does not update FK definition.
3 --echo #
4
5 --echo
6 --echo # Test that it's not possible to rename columns participating in a
7 --echo # foreign key (either in the referencing or referenced table).
8 --echo
9
10 CREATE TABLE t1 (a INT PRIMARY KEY, b INT) ROW_FORMAT=COMPACT ENGINE=INNODB;
11
12 CREATE TABLE t2 (a INT PRIMARY KEY, b INT,
13 CONSTRAINT fk1 FOREIGN KEY (a) REFERENCES t1(a))
14 ROW_FORMAT=COMPACT ENGINE=INNODB;
15
16 CREATE TABLE t3 (a INT PRIMARY KEY, b INT, KEY(b), C INT,
17 CONSTRAINT fk2 FOREIGN KEY (b) REFERENCES t3 (a))
18 ROW_FORMAT=COMPACT ENGINE=INNODB;
19
20 INSERT INTO t1 VALUES (1,1),(2,2),(3,3);
21 INSERT INTO t2 VALUES (1,1),(2,2),(3,3);
22 INSERT INTO t3 VALUES (1,1,1),(2,2,2),(3,3,3);
23
24 --echo
25 --echo # Test renaming the column in the referenced table.
26 --echo
27
28 --enable_info
29 ALTER TABLE t1 CHANGE a e INT;
30
31 --echo # Ensure that online column rename works.
32
33 ALTER TABLE t1 CHANGE b c INT;
34
35 --echo
36 --echo # Test renaming the column in the referencing table
37 --echo
38
39 ALTER TABLE t2 CHANGE a z INT;
40
41 --echo # Ensure that online column rename works.
42
43 ALTER TABLE t2 CHANGE b c INT;
44
45 --echo
46 --echo # Test with self-referential constraints
47 --echo
48
49 ALTER TABLE t3 CHANGE a f INT;
50
51 ALTER TABLE t3 CHANGE b g INT;
52
53 --echo # Ensure that online column rename works.
54
55 ALTER TABLE t3 CHANGE c d INT;
56
57 --echo
58 --echo # Cleanup.
59 --echo
60
61 --disable_info
62 SHOW CREATE TABLE t1;
63 SHOW CREATE TABLE t2;
64 SHOW CREATE TABLE t3;
65
66 --sorted_result
67 SELECT f.FOR_NAME, f.REF_NAME, f.N_COLS, f.TYPE, c.FOR_COL_NAME, c.REF_COL_NAME, c.POS
68 FROM INFORMATION_SCHEMA.INNODB_FOREIGN_COLS c
69 INNER JOIN INFORMATION_SCHEMA.INNODB_FOREIGN f
70 ON c.ID=f.ID
71 WHERE FOR_NAME LIKE 'test/t%';
72
73 DROP TABLE t3;
74 DROP TABLE t2;
75 DROP TABLE t1;