Debian Codegraph @

mysql-test/suite/ndb_ddl/alter_drop_fk_copy.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 # Verify the foreign keys on parent
12 SHOW CREATE TABLE test.parent;
13 Table Create Table
14 parent CREATE TABLE `parent` (
15 `pk` int NOT NULL,
16 `id1` int DEFAULT NULL,
17 `id2` int DEFAULT NULL,
18 `ref1` int DEFAULT NULL,
19 `ref2` int DEFAULT NULL,
20 PRIMARY KEY (`pk`),
21 UNIQUE KEY `id1` (`id1`),
22 UNIQUE KEY `id2` (`id2`),
23 KEY `fk_p1` (`ref1`),
24 KEY `ref2_idx` (`ref2`),
25 CONSTRAINT `fk_p1` FOREIGN KEY (`ref1`) REFERENCES `parent` (`id1`),
26 CONSTRAINT `parent_fk_1` FOREIGN KEY (`ref2`) REFERENCES `parent` (`id2`)
27 ) ENGINE=ndbcluster DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
28 Foreign keys consistent across NDB and all connected mysqld's DDs
29 create table child (
30 pk int primary key,
31 ref1 int,
32 ref2 int,
33 constraint fk_c1 foreign key ref1_idx(ref1) references parent (id1),
34 foreign key ref2_idx(ref2) references parent (id2)
35 ) engine ndb;
36 # Verify the foreign keys on child
37 SHOW CREATE TABLE test.child;
38 Table Create Table
39 child CREATE TABLE `child` (
40 `pk` int NOT NULL,
41 `ref1` int DEFAULT NULL,
42 `ref2` int DEFAULT NULL,
43 PRIMARY KEY (`pk`),
44 KEY `fk_c1` (`ref1`),
45 KEY `ref2_idx` (`ref2`),
46 CONSTRAINT `child_fk_1` FOREIGN KEY (`ref2`) REFERENCES `parent` (`id2`),
47 CONSTRAINT `fk_c1` FOREIGN KEY (`ref1`) REFERENCES `parent` (`id1`)
48 ) ENGINE=ndbcluster DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
49 Foreign keys consistent across NDB and all connected mysqld's DDs
50 # Drop foreign keys one by one and verify the other FKs integrity
51 alter table parent
52 drop foreign key fk_p1,
53 algorithm = copy;
54 # Verify the foreign keys on parent and child
55 SHOW CREATE TABLE test.parent;
56 Table Create Table
57 parent CREATE TABLE `parent` (
58 `pk` int NOT NULL,
59 `id1` int DEFAULT NULL,
60 `id2` int DEFAULT NULL,
61 `ref1` int DEFAULT NULL,
62 `ref2` int DEFAULT NULL,
63 PRIMARY KEY (`pk`),
64 UNIQUE KEY `id1` (`id1`),
65 UNIQUE KEY `id2` (`id2`),
66 KEY `fk_p1` (`ref1`),
67 KEY `ref2_idx` (`ref2`),
68 CONSTRAINT `parent_fk_1` FOREIGN KEY (`ref2`) REFERENCES `parent` (`id2`)
69 ) ENGINE=ndbcluster DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
70 Foreign keys consistent across NDB and all connected mysqld's DDs
71 SHOW CREATE TABLE test.child;
72 Table Create Table
73 child CREATE TABLE `child` (
74 `pk` int NOT NULL,
75 `ref1` int DEFAULT NULL,
76 `ref2` int DEFAULT NULL,
77 PRIMARY KEY (`pk`),
78 KEY `fk_c1` (`ref1`),
79 KEY `ref2_idx` (`ref2`),
80 CONSTRAINT `child_fk_1` FOREIGN KEY (`ref2`) REFERENCES `parent` (`id2`),
81 CONSTRAINT `fk_c1` FOREIGN KEY (`ref1`) REFERENCES `parent` (`id1`)
82 ) ENGINE=ndbcluster DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
83 Foreign keys consistent across NDB and all connected mysqld's DDs
84 alter table parent
85 drop foreign key parent_fk_1,
86 algorithm = copy;
87 # Verify the foreign keys on parent and child
88 SHOW CREATE TABLE test.parent;
89 Table Create Table
90 parent CREATE TABLE `parent` (
91 `pk` int NOT NULL,
92 `id1` int DEFAULT NULL,
93 `id2` int DEFAULT NULL,
94 `ref1` int DEFAULT NULL,
95 `ref2` int DEFAULT NULL,
96 PRIMARY KEY (`pk`),
97 UNIQUE KEY `id1` (`id1`),
98 UNIQUE KEY `id2` (`id2`),
99 KEY `fk_p1` (`ref1`),
100 KEY `ref2_idx` (`ref2`)
101 ) ENGINE=ndbcluster DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
102 No foreign keys found on table `test.parent`
103 SHOW CREATE TABLE test.child;
104 Table Create Table
105 child CREATE TABLE `child` (
106 `pk` int NOT NULL,
107 `ref1` int DEFAULT NULL,
108 `ref2` int DEFAULT NULL,
109 PRIMARY KEY (`pk`),
110 KEY `fk_c1` (`ref1`),
111 KEY `ref2_idx` (`ref2`),
112 CONSTRAINT `child_fk_1` FOREIGN KEY (`ref2`) REFERENCES `parent` (`id2`),
113 CONSTRAINT `fk_c1` FOREIGN KEY (`ref1`) REFERENCES `parent` (`id1`)
114 ) ENGINE=ndbcluster DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
115 Foreign keys consistent across NDB and all connected mysqld's DDs
116 alter table child
117 drop foreign key fk_c1,
118 algorithm = copy;
119 # Verify the foreign keys on child
120 SHOW CREATE TABLE test.child;
121 Table Create Table
122 child CREATE TABLE `child` (
123 `pk` int NOT NULL,
124 `ref1` int DEFAULT NULL,
125 `ref2` int DEFAULT NULL,
126 PRIMARY KEY (`pk`),
127 KEY `fk_c1` (`ref1`),
128 KEY `ref2_idx` (`ref2`),
129 CONSTRAINT `child_fk_1` FOREIGN KEY (`ref2`) REFERENCES `parent` (`id2`)
130 ) ENGINE=ndbcluster DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
131 Foreign keys consistent across NDB and all connected mysqld's DDs
132 alter table child
133 drop foreign key child_fk_1,
134 algorithm = copy;
135 # Verify the foreign keys on child
136 SHOW CREATE TABLE test.child;
137 Table Create Table
138 child CREATE TABLE `child` (
139 `pk` int NOT NULL,
140 `ref1` int DEFAULT NULL,
141 `ref2` int DEFAULT NULL,
142 PRIMARY KEY (`pk`),
143 KEY `fk_c1` (`ref1`),
144 KEY `ref2_idx` (`ref2`)
145 ) ENGINE=ndbcluster DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
146 No foreign keys found on table `test.child`
147 # Cleanup
148 drop table child, parent;