Debian Codegraph @

mysql-test/suite/galera/r/galera_fk_cascade_delete.result

raw
1 connection node_2;
2 connection node_1;
3 #
4 # test phase with cascading foreign key through 3 tables
5 #
6 connection node_1;
7 CREATE TABLE grandparent (
8 id INT NOT NULL PRIMARY KEY
9 ) ENGINE=InnoDB;
10 CREATE TABLE parent (
11 id INT NOT NULL PRIMARY KEY,
12 grandparent_id INT,
13 FOREIGN KEY (grandparent_id)
14 REFERENCES grandparent(id)
15 ON DELETE CASCADE
16 ) ENGINE=InnoDB;
17 CREATE TABLE child (
18 id INT NOT NULL PRIMARY KEY,
19 parent_id INT,
20 FOREIGN KEY (parent_id)
21 REFERENCES parent(id)
22 ON DELETE CASCADE
23 ) ENGINE=InnoDB;
24 INSERT INTO grandparent VALUES (1),(2);
25 INSERT INTO parent VALUES (1,1), (2,2);
26 INSERT INTO child VALUES (1,1), (2,2);
27 connection node_2;
28 DELETE FROM grandparent WHERE id = 1;
29 SELECT * FROM grandparent;
30 id
31 2
32 SELECT * FROM parent;
33 id grandparent_id
34 2 2
35 SELECT * FROM child;
36 id parent_id
37 2 2
38 connection node_1;
39 SELECT * FROM grandparent;
40 id
41 2
42 SELECT * FROM parent;
43 id grandparent_id
44 2 2
45 SELECT * FROM child;
46 id parent_id
47 2 2
48 DROP TABLE child;
49 DROP TABLE parent;
50 DROP TABLE grandparent;
51
52 Scenario 2, testing PA applying with FK cascade delete
53
54 CREATE TABLE p1 (f1 INTEGER PRIMARY KEY, f2 INTEGER) ENGINE=INNODB;
55 CREATE TABLE p2 (f1 INTEGER PRIMARY KEY, f2 INTEGER) ENGINE=INNODB;
56 CREATE TABLE c (f1 INTEGER PRIMARY KEY, p1_id INTEGER, p2_id INTEGER,
57 f2 INTEGER,
58 CONSTRAINT fk_1 FOREIGN KEY (p1_id) REFERENCES p1 (f1)
59 ON DELETE CASCADE,
60 CONSTRAINT fk_2 FOREIGN KEY (p2_id) REFERENCES p2 (f1)
61 ON DELETE CASCADE);
62 connection node_2;
63 set global wsrep_slave_threads=DEFAULT;
64 SELECT * FROM p1;
65 f1 f2
66 SELECT * FROM p2;
67 f1 f2
68 SELECT * FROM c;
69 f1 p1_id p2_id f2
70 connection node_1;
71 DROP TABLE c;
72 DROP TABLE p1,p2;
73
74 Scenario 4, testing PA applying with FK cascade delete on
75 more than one level
76
77 CREATE TABLE gp1 (f1 INTEGER PRIMARY KEY, f2 INTEGER) ENGINE=INNODB;
78 CREATE TABLE gp2 (f1 INTEGER PRIMARY KEY, f2 INTEGER) ENGINE=INNODB;
79 CREATE TABLE p1 (f1 INTEGER PRIMARY KEY, p1_id INTEGER, p2_id INTEGER,
80 f2 INTEGER,
81 CONSTRAINT pfk_3 FOREIGN KEY (p1_id) REFERENCES gp1 (f1)
82 ON DELETE CASCADE
83 ) ENGINE=INNODB;
84 CREATE TABLE p2 (f1 INTEGER PRIMARY KEY,p1_id INTEGER, p2_id INTEGER,
85 f2 INTEGER,
86 CONSTRAINT pfk_4 FOREIGN KEY (p1_id) REFERENCES gp2 (f1)
87 ON DELETE CASCADE
88 ) ENGINE=INNODB;
89 CREATE TABLE c (f1 INTEGER PRIMARY KEY, p1_id INTEGER, p2_id INTEGER,
90 f2 INTEGER,
91 CONSTRAINT fk_1 FOREIGN KEY (p1_id) REFERENCES p1 (f1)
92 ON DELETE CASCADE,
93 CONSTRAINT fk_2 FOREIGN KEY (p2_id) REFERENCES p2 (f1)
94 ON DELETE CASCADE) ENGINE=INNODB;
95 connection node_2;
96 set global wsrep_slave_threads=DEFAULT;
97 SELECT * FROM gp1;
98 f1 f2
99 SELECT * FROM gp2;
100 f1 f2
101 SELECT * FROM p1;
102 f1 p1_id p2_id f2
103 SELECT * FROM p2;
104 f1 p1_id p2_id f2
105 SELECT * FROM c;
106 f1 p1_id p2_id f2
107 connection node_1;
108 DROP TABLE c;
109 DROP TABLE p1,p2;
110 DROP TABLE gp1,gp2;
111
112 Scenario 3, testing PA applying with FK cascade delete on
113 more than one level in a diamond topology
114
115 CREATE TABLE ggp1 (f1 INTEGER PRIMARY KEY, f2 INTEGER) ENGINE=INNODB;
116 CREATE TABLE gp1 (f1 INTEGER PRIMARY KEY, p1_id INTEGER, p2_id INTEGER,
117 f2 INTEGER,
118 CONSTRAINT pfk_6 FOREIGN KEY (p1_id) REFERENCES ggp1 (f1)
119 ON DELETE CASCADE
120 ) ENGINE=INNODB;
121 CREATE TABLE gp2 (f1 INTEGER PRIMARY KEY, p1_id INTEGER, p2_id INTEGER,
122 f2 INTEGER,
123 CONSTRAINT pfk_5 FOREIGN KEY (p1_id) REFERENCES ggp1 (f1)
124 ON DELETE CASCADE
125 ) ENGINE=INNODB;
126 CREATE TABLE p1 (f1 INTEGER PRIMARY KEY, p1_id INTEGER, p2_id INTEGER,
127 f2 INTEGER,
128 CONSTRAINT pfk_3 FOREIGN KEY (p1_id) REFERENCES gp1 (f1)
129 ON DELETE CASCADE
130 ) ENGINE=INNODB;
131 CREATE TABLE p2 (f1 INTEGER PRIMARY KEY,p1_id INTEGER, p2_id INTEGER,
132 f2 INTEGER,
133 CONSTRAINT pfk_4 FOREIGN KEY (p1_id) REFERENCES gp2 (f1)
134 ON DELETE CASCADE
135 ) ENGINE=INNODB;
136 CREATE TABLE c (f1 INTEGER PRIMARY KEY, p1_id INTEGER, p2_id INTEGER,
137 f2 INTEGER,
138 CONSTRAINT fk_1 FOREIGN KEY (p1_id) REFERENCES p1 (f1)
139 ON DELETE CASCADE,
140 CONSTRAINT fk_2 FOREIGN KEY (p2_id) REFERENCES p2 (f1)
141 ON DELETE CASCADE) ENGINE=INNODB;
142 connection node_2;
143 set global wsrep_slave_threads=DEFAULT;
144 SELECT * FROM ggp1;
145 f1 f2
146 SELECT * FROM gp2;
147 f1 p1_id p2_id f2
148 SELECT * FROM gp1;
149 f1 p1_id p2_id f2
150 SELECT * FROM p1;
151 f1 p1_id p2_id f2
152 SELECT * FROM p2;
153 f1 p1_id p2_id f2
154 SELECT * FROM c;
155 f1 p1_id p2_id f2
156 connection node_1;
157 DROP TABLE c;
158 DROP TABLE p1,p2;
159 DROP TABLE gp1,gp2;
160 DROP TABLE ggp1;