Debian Codegraph @

mysql-test/suite/ndb_ddl/alter_add_fk_inplace.result

raw
1 # Setup
2 create table parent (
3 pk int primary key,
4 id1 int unique,
5 id2 int unique,
6 id3 int unique,
7 ref1 int,
8 ref2 int,
9 ref3 int
10 ) engine ndb;
11 create table child (
12 pk int primary key,
13 ref1 int,
14 ref2 int
15 ) engine ndb;
16 # Add self referential foreign keys
17 # Add one with a user specified constraint name
18 alter table parent
19 add constraint fk_p1 foreign key ref1_idx(ref1) references parent (id1),
20 algorithm = inplace;
21 # Verify the foreign keys on parent
22 SHOW CREATE TABLE test.parent;
23 Table Create Table
24 parent CREATE TABLE `parent` (
25 `pk` int NOT NULL,
26 `id1` int DEFAULT NULL,
27 `id2` int DEFAULT NULL,
28 `id3` int DEFAULT NULL,
29 `ref1` int DEFAULT NULL,
30 `ref2` int DEFAULT NULL,
31 `ref3` int DEFAULT NULL,
32 PRIMARY KEY (`pk`),
33 UNIQUE KEY `id1` (`id1`),
34 UNIQUE KEY `id2` (`id2`),
35 UNIQUE KEY `id3` (`id3`),
36 KEY `fk_p1` (`ref1`),
37 CONSTRAINT `fk_p1` FOREIGN KEY (`ref1`) REFERENCES `parent` (`id1`)
38 ) ENGINE=ndbcluster DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
39 Foreign keys consistent across NDB and all connected mysqld's DDs
40 # Add one with generated constraint name
41 alter table parent
42 add foreign key ref2_idx(ref2) references parent (id2),
43 algorithm = inplace;
44 # Verify the foreign keys on parent
45 SHOW CREATE TABLE test.parent;
46 Table Create Table
47 parent CREATE TABLE `parent` (
48 `pk` int NOT NULL,
49 `id1` int DEFAULT NULL,
50 `id2` int DEFAULT NULL,
51 `id3` int DEFAULT NULL,
52 `ref1` int DEFAULT NULL,
53 `ref2` int DEFAULT NULL,
54 `ref3` int DEFAULT NULL,
55 PRIMARY KEY (`pk`),
56 UNIQUE KEY `id1` (`id1`),
57 UNIQUE KEY `id2` (`id2`),
58 UNIQUE KEY `id3` (`id3`),
59 KEY `fk_p1` (`ref1`),
60 KEY `ref2_idx` (`ref2`),
61 CONSTRAINT `fk_p1` FOREIGN KEY (`ref1`) REFERENCES `parent` (`id1`),
62 CONSTRAINT `parent_fk_1` FOREIGN KEY (`ref2`) REFERENCES `parent` (`id2`)
63 ) ENGINE=ndbcluster DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
64 Foreign keys consistent across NDB and all connected mysqld's DDs
65 # Add foreign keys on child referencing parent
66 # Add one with a user specified constraint name
67 alter table child
68 add constraint fk_c1 foreign key ref1_idx(ref1) references parent (id1),
69 algorithm = inplace;
70 # Verify the foreign keys on child
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 CONSTRAINT `fk_c1` FOREIGN KEY (`ref1`) REFERENCES `parent` (`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 # Add one with generated constraint name
83 alter table child
84 add foreign key ref2_idx(ref2) references parent (id2),
85 algorithm = inplace;
86 # Verify the foreign keys on child
87 SHOW CREATE TABLE test.child;
88 Table Create Table
89 child CREATE TABLE `child` (
90 `pk` int NOT NULL,
91 `ref1` int DEFAULT NULL,
92 `ref2` int DEFAULT NULL,
93 PRIMARY KEY (`pk`),
94 KEY `fk_c1` (`ref1`),
95 KEY `ref2_idx` (`ref2`),
96 CONSTRAINT `child_fk_1` FOREIGN KEY (`ref2`) REFERENCES `parent` (`id2`),
97 CONSTRAINT `fk_c1` FOREIGN KEY (`ref1`) REFERENCES `parent` (`id1`)
98 ) ENGINE=ndbcluster DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
99 Foreign keys consistent across NDB and all connected mysqld's DDs
100 # Add a foreign key to the parent and verify that the
101 # exisiting foreign key constraints are consistent
102 alter table parent
103 add foreign key ref3_idx(ref3) references parent (id3),
104 algorithm = inplace;
105 # Verify the foreign keys on parent and child
106 SHOW CREATE TABLE test.parent;
107 Table Create Table
108 parent CREATE TABLE `parent` (
109 `pk` int NOT NULL,
110 `id1` int DEFAULT NULL,
111 `id2` int DEFAULT NULL,
112 `id3` int DEFAULT NULL,
113 `ref1` int DEFAULT NULL,
114 `ref2` int DEFAULT NULL,
115 `ref3` int DEFAULT NULL,
116 PRIMARY KEY (`pk`),
117 UNIQUE KEY `id1` (`id1`),
118 UNIQUE KEY `id2` (`id2`),
119 UNIQUE KEY `id3` (`id3`),
120 KEY `fk_p1` (`ref1`),
121 KEY `ref2_idx` (`ref2`),
122 KEY `ref3_idx` (`ref3`),
123 CONSTRAINT `fk_p1` FOREIGN KEY (`ref1`) REFERENCES `parent` (`id1`),
124 CONSTRAINT `parent_fk_1` FOREIGN KEY (`ref2`) REFERENCES `parent` (`id2`),
125 CONSTRAINT `parent_fk_2` FOREIGN KEY (`ref3`) REFERENCES `parent` (`id3`)
126 ) ENGINE=ndbcluster DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
127 Foreign keys consistent across NDB and all connected mysqld's DDs
128 SHOW CREATE TABLE test.child;
129 Table Create Table
130 child CREATE TABLE `child` (
131 `pk` int NOT NULL,
132 `ref1` int DEFAULT NULL,
133 `ref2` int DEFAULT NULL,
134 PRIMARY KEY (`pk`),
135 KEY `fk_c1` (`ref1`),
136 KEY `ref2_idx` (`ref2`),
137 CONSTRAINT `child_fk_1` FOREIGN KEY (`ref2`) REFERENCES `parent` (`id2`),
138 CONSTRAINT `fk_c1` FOREIGN KEY (`ref1`) REFERENCES `parent` (`id1`)
139 ) ENGINE=ndbcluster DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
140 Foreign keys consistent across NDB and all connected mysqld's DDs
141 # Cleanup
142 drop table child, parent;