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