Debian Codegraph @

mysql-test/suite/ndb_ddl/alter_rename_update_fk_name.result

raw
1 # Setup
2 create table parent (
3 pk int primary key,
4 id1 int unique,
5 id2 int unique,
6 ref1 int,
7 ref2 int,
8 constraint fk_p1 foreign key ref1_idx(ref1) references parent (id1),
9 foreign key ref2_idx(ref2) references parent (id2)
10 ) engine ndb;
11 create table child (
12 pk int primary key,
13 ref1 int,
14 ref2 int,
15 constraint fk_c1 foreign key ref1_idx(ref1) references parent (id1),
16 foreign key ref2_idx(ref2) references parent (id2)
17 ) engine ndb;
18 # Verify the foreign keys on parent and child
19 SHOW CREATE TABLE test.parent;
20 Table Create Table
21 parent CREATE TABLE `parent` (
22 `pk` int NOT NULL,
23 `id1` int DEFAULT NULL,
24 `id2` int DEFAULT NULL,
25 `ref1` int DEFAULT NULL,
26 `ref2` int DEFAULT NULL,
27 PRIMARY KEY (`pk`),
28 UNIQUE KEY `id1` (`id1`),
29 UNIQUE KEY `id2` (`id2`),
30 KEY `fk_p1` (`ref1`),
31 KEY `ref2_idx` (`ref2`),
32 CONSTRAINT `fk_p1` FOREIGN KEY (`ref1`) REFERENCES `parent` (`id1`),
33 CONSTRAINT `parent_fk_1` FOREIGN KEY (`ref2`) REFERENCES `parent` (`id2`)
34 ) ENGINE=ndbcluster DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
35 Foreign keys consistent across NDB and all connected mysqld's DDs
36 SHOW CREATE TABLE test.child;
37 Table Create Table
38 child CREATE TABLE `child` (
39 `pk` int NOT NULL,
40 `ref1` int DEFAULT NULL,
41 `ref2` int DEFAULT NULL,
42 PRIMARY KEY (`pk`),
43 KEY `fk_c1` (`ref1`),
44 KEY `ref2_idx` (`ref2`),
45 CONSTRAINT `child_fk_1` FOREIGN KEY (`ref2`) REFERENCES `parent` (`id2`),
46 CONSTRAINT `fk_c1` FOREIGN KEY (`ref1`) REFERENCES `parent` (`id1`)
47 ) ENGINE=ndbcluster DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
48 Foreign keys consistent across NDB and all connected mysqld's DDs
49 # Simple rename parent
50 rename table parent to parent1;
51 # Verify the foreign keys on parent and child
52 SHOW CREATE TABLE test.parent1;
53 Table Create Table
54 parent1 CREATE TABLE `parent1` (
55 `pk` int NOT NULL,
56 `id1` int DEFAULT NULL,
57 `id2` int DEFAULT NULL,
58 `ref1` int DEFAULT NULL,
59 `ref2` int DEFAULT NULL,
60 PRIMARY KEY (`pk`),
61 UNIQUE KEY `id1` (`id1`),
62 UNIQUE KEY `id2` (`id2`),
63 KEY `fk_p1` (`ref1`),
64 KEY `ref2_idx` (`ref2`),
65 CONSTRAINT `fk_p1` FOREIGN KEY (`ref1`) REFERENCES `parent1` (`id1`),
66 CONSTRAINT `parent1_fk_1` FOREIGN KEY (`ref2`) REFERENCES `parent1` (`id2`)
67 ) ENGINE=ndbcluster DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
68 Foreign keys consistent across NDB and all connected mysqld's DDs
69 SHOW CREATE TABLE test.child;
70 Table Create Table
71 child CREATE TABLE `child` (
72 `pk` int NOT NULL,
73 `ref1` int DEFAULT NULL,
74 `ref2` int DEFAULT NULL,
75 PRIMARY KEY (`pk`),
76 KEY `fk_c1` (`ref1`),
77 KEY `ref2_idx` (`ref2`),
78 CONSTRAINT `child_fk_1` FOREIGN KEY (`ref2`) REFERENCES `parent1` (`id2`),
79 CONSTRAINT `fk_c1` FOREIGN KEY (`ref1`) REFERENCES `parent1` (`id1`)
80 ) ENGINE=ndbcluster DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
81 Foreign keys consistent across NDB and all connected mysqld's DDs
82 # Rename parent using copy algorithm
83 alter table parent1 rename to parent2, algorithm = copy;
84 # Verify the foreign keys on parent and child
85 SHOW CREATE TABLE test.parent2;
86 Table Create Table
87 parent2 CREATE TABLE `parent2` (
88 `pk` int NOT NULL,
89 `id1` int DEFAULT NULL,
90 `id2` int DEFAULT NULL,
91 `ref1` int DEFAULT NULL,
92 `ref2` int DEFAULT NULL,
93 PRIMARY KEY (`pk`),
94 UNIQUE KEY `id1` (`id1`),
95 UNIQUE KEY `id2` (`id2`),
96 KEY `fk_p1` (`ref1`),
97 KEY `ref2_idx` (`ref2`),
98 CONSTRAINT `fk_p1` FOREIGN KEY (`ref1`) REFERENCES `parent2` (`id1`),
99 CONSTRAINT `parent2_fk_1` FOREIGN KEY (`ref2`) REFERENCES `parent2` (`id2`)
100 ) ENGINE=ndbcluster DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
101 Foreign keys consistent across NDB and all connected mysqld's DDs
102 SHOW CREATE TABLE test.child;
103 Table Create Table
104 child CREATE TABLE `child` (
105 `pk` int NOT NULL,
106 `ref1` int DEFAULT NULL,
107 `ref2` int DEFAULT NULL,
108 PRIMARY KEY (`pk`),
109 KEY `fk_c1` (`ref1`),
110 KEY `ref2_idx` (`ref2`),
111 CONSTRAINT `child_fk_1` FOREIGN KEY (`ref2`) REFERENCES `parent2` (`id2`),
112 CONSTRAINT `fk_c1` FOREIGN KEY (`ref1`) REFERENCES `parent2` (`id1`)
113 ) ENGINE=ndbcluster DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
114 Foreign keys consistent across NDB and all connected mysqld's DDs
115 # Rename parent using inplace algorithm
116 alter table parent2 rename to parent3, algorithm = inplace;
117 # Verify the foreign keys on parent and child
118 SHOW CREATE TABLE test.parent3;
119 Table Create Table
120 parent3 CREATE TABLE `parent3` (
121 `pk` int NOT NULL,
122 `id1` int DEFAULT NULL,
123 `id2` int DEFAULT NULL,
124 `ref1` int DEFAULT NULL,
125 `ref2` int DEFAULT NULL,
126 PRIMARY KEY (`pk`),
127 UNIQUE KEY `id1` (`id1`),
128 UNIQUE KEY `id2` (`id2`),
129 KEY `fk_p1` (`ref1`),
130 KEY `ref2_idx` (`ref2`),
131 CONSTRAINT `fk_p1` FOREIGN KEY (`ref1`) REFERENCES `parent3` (`id1`),
132 CONSTRAINT `parent3_fk_1` FOREIGN KEY (`ref2`) REFERENCES `parent3` (`id2`)
133 ) ENGINE=ndbcluster DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
134 Foreign keys consistent across NDB and all connected mysqld's DDs
135 SHOW CREATE TABLE test.child;
136 Table Create Table
137 child CREATE TABLE `child` (
138 `pk` int NOT NULL,
139 `ref1` int DEFAULT NULL,
140 `ref2` int DEFAULT NULL,
141 PRIMARY KEY (`pk`),
142 KEY `fk_c1` (`ref1`),
143 KEY `ref2_idx` (`ref2`),
144 CONSTRAINT `child_fk_1` FOREIGN KEY (`ref2`) REFERENCES `parent3` (`id2`),
145 CONSTRAINT `fk_c1` FOREIGN KEY (`ref1`) REFERENCES `parent3` (`id1`)
146 ) ENGINE=ndbcluster DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
147 Foreign keys consistent across NDB and all connected mysqld's DDs
148 # Simple rename child
149 rename table child to child1;
150 # Verify the foreign keys on child
151 SHOW CREATE TABLE test.child1;
152 Table Create Table
153 child1 CREATE TABLE `child1` (
154 `pk` int NOT NULL,
155 `ref1` int DEFAULT NULL,
156 `ref2` int DEFAULT NULL,
157 PRIMARY KEY (`pk`),
158 KEY `fk_c1` (`ref1`),
159 KEY `ref2_idx` (`ref2`),
160 CONSTRAINT `child1_fk_1` FOREIGN KEY (`ref2`) REFERENCES `parent3` (`id2`),
161 CONSTRAINT `fk_c1` FOREIGN KEY (`ref1`) REFERENCES `parent3` (`id1`)
162 ) ENGINE=ndbcluster DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
163 Foreign keys consistent across NDB and all connected mysqld's DDs
164 # Rename child using copy algorithm
165 alter table child1 rename to child2, algorithm = copy;
166 # Verify the foreign keys on child
167 SHOW CREATE TABLE test.child2;
168 Table Create Table
169 child2 CREATE TABLE `child2` (
170 `pk` int NOT NULL,
171 `ref1` int DEFAULT NULL,
172 `ref2` int DEFAULT NULL,
173 PRIMARY KEY (`pk`),
174 KEY `fk_c1` (`ref1`),
175 KEY `ref2_idx` (`ref2`),
176 CONSTRAINT `child2_fk_1` FOREIGN KEY (`ref2`) REFERENCES `parent3` (`id2`),
177 CONSTRAINT `fk_c1` FOREIGN KEY (`ref1`) REFERENCES `parent3` (`id1`)
178 ) ENGINE=ndbcluster DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
179 Foreign keys consistent across NDB and all connected mysqld's DDs
180 # Rename child using inplace algorithm
181 alter table child2 rename to child3, algorithm = inplace;
182 # Verify the foreign keys on child
183 SHOW CREATE TABLE test.child3;
184 Table Create Table
185 child3 CREATE TABLE `child3` (
186 `pk` int NOT NULL,
187 `ref1` int DEFAULT NULL,
188 `ref2` int DEFAULT NULL,
189 PRIMARY KEY (`pk`),
190 KEY `fk_c1` (`ref1`),
191 KEY `ref2_idx` (`ref2`),
192 CONSTRAINT `child3_fk_1` FOREIGN KEY (`ref2`) REFERENCES `parent3` (`id2`),
193 CONSTRAINT `fk_c1` FOREIGN KEY (`ref1`) REFERENCES `parent3` (`id1`)
194 ) ENGINE=ndbcluster DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
195 Foreign keys consistent across NDB and all connected mysqld's DDs
196 # Cleanup
197 drop table child3, parent3;