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
|
SET @saved_sql_log_bin = @@SESSION.sql_log_bin;
#
# CREATE TABLE AS SELECT (CTAS) and foreign key (FK).
#
CREATE TABLE t0 (f1 INT PRIMARY KEY);
INSERT INTO t0 VALUES (1),(2),(3),(4);
#
# CASE 1 The behavior of non-atomic CTAS remains the same
# with request to create FK. There is no engine which do
# not support atomic DDL, but supports foreign keys.
# MyISAM does not support foreign keys, so there is no error
# as it ignore FK constraints silently.
CREATE TABLE myisam_table1 (m INT, n INT, FOREIGN KEY (n) REFERENCES t0(f1))
ENGINE=MyISAM AS SELECT 101 as m, 5 as n;
SHOW CREATE TABLE myisam_table1;
Table Create Table
myisam_table1 CREATE TABLE `myisam_table1` (
`m` int DEFAULT NULL,
`n` int DEFAULT NULL,
KEY `n` (`n`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
CREATE TABLE myisam_table2 (m INT, n INT, FOREIGN KEY (n) REFERENCES t0(f1))
ENGINE=MyISAM AS SELECT 101 as m, 2 as n;
SHOW CREATE TABLE myisam_table2;
Table Create Table
myisam_table2 CREATE TABLE `myisam_table2` (
`m` int DEFAULT NULL,
`n` int DEFAULT NULL,
KEY `n` (`n`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
DROP TABLE myisam_table1;
DROP TABLE myisam_table2;
#
# CASE 2 The behavior of atomic CTAS, with sql_log_bin OFF.
#
SET sql_log_bin = OFF;
CREATE TABLE innodb_table1 (m INT, n INT, FOREIGN KEY (n) REFERENCES t0(f1))
AS SELECT 101 as m, 5 as n;
ERROR 23000: Cannot add or update a child row: a foreign key constraint fails (`test`.`innodb_table1`, CONSTRAINT `innodb_table1_ibfk_1` FOREIGN KEY (`n`) REFERENCES `t0` (`f1`))
CREATE TABLE innodb_table1 (m INT, n INT, FOREIGN KEY (n) REFERENCES t0(f1))
AS SELECT 101 as m, 2 as n;
CREATE TABLE innodb_table2 as SELECT m, 4 FROM innodb_table1;
SHOW CREATE TABLE innodb_table1;
Table Create Table
innodb_table1 CREATE TABLE `innodb_table1` (
`m` int DEFAULT NULL,
`n` int DEFAULT NULL,
KEY `n` (`n`),
CONSTRAINT `innodb_table1_ibfk_1` FOREIGN KEY (`n`) REFERENCES `t0` (`f1`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
SHOW CREATE TABLE innodb_table2;
Table Create Table
innodb_table2 CREATE TABLE `innodb_table2` (
`m` int DEFAULT NULL,
`4` int NOT NULL DEFAULT '0'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
DROP TABLE innodb_table1;
DROP TABLE innodb_table2;
#
# CASE 3 The behavior of CTAS, with sql_log_bin ON and format STATEMENT.
#
SET sql_log_bin = ON;
SET @@SESSION.binlog_format=STATEMENT;
Warnings:
Warning 1287 '@@binlog_format' is deprecated and will be removed in a future release.
CREATE TABLE innodb_table1 (m INT, n INT, FOREIGN KEY (n) REFERENCES t0(f1))
AS SELECT 101 as m, 5 as n;
ERROR 23000: Cannot add or update a child row: a foreign key constraint fails (`test`.`innodb_table1`, CONSTRAINT `innodb_table1_ibfk_1` FOREIGN KEY (`n`) REFERENCES `t0` (`f1`))
CREATE TABLE innodb_table1 (m INT, n INT, FOREIGN KEY (n) REFERENCES t0(f1))
AS SELECT 101 as m, 2 as n;
DROP TABLE innodb_table1;
#
# CASE 4 The behavior of CTAS, with sql_log_bin ON and format MIXED.
# The behavior would be same as case 3 above.
SET @@SESSION.binlog_format=MIXED;
Warnings:
Warning 1287 '@@binlog_format' is deprecated and will be removed in a future release.
CREATE TABLE innodb_table1 (m INT, n INT, FOREIGN KEY (n) REFERENCES t0(f1))
AS SELECT 101 as m, 5 as n;
ERROR 23000: Cannot add or update a child row: a foreign key constraint fails (`test`.`innodb_table1`, CONSTRAINT `innodb_table1_ibfk_1` FOREIGN KEY (`n`) REFERENCES `t0` (`f1`))
CREATE TABLE innodb_table1 (m INT, n INT, FOREIGN KEY (n) REFERENCES t0(f1))
AS SELECT 101 as m, 2 as n;
DROP TABLE innodb_table1;
#
# CASE 5 The behavior of CTAS, with sql_log_bin ON and format ROW.
#
SET @@SESSION.binlog_format=ROW;
Warnings:
Warning 1287 '@@binlog_format' is deprecated and will be removed in a future release.
CREATE TABLE innodb_table1 (m INT, n INT, FOREIGN KEY (n) REFERENCES t0(f1))
AS SELECT 101 as m, 5 as n;
ERROR HY000: Foreign key creation is not allowed with CREATE TABLE as SELECT and CREATE TABLE with START TRANSACTION statement.
CREATE TABLE innodb_table1 (m INT, n INT, FOREIGN KEY (n) REFERENCES t0(f1))
AS SELECT 101 as m, 2 as n;
ERROR HY000: Foreign key creation is not allowed with CREATE TABLE as SELECT and CREATE TABLE with START TRANSACTION statement.
CREATE TABLE innodb_table1 (m INT, n INT,
FOREIGN KEY (n) REFERENCES t0(f1)) START TRANSACTION;
ERROR HY000: Foreign key creation is not allowed with CREATE TABLE as SELECT and CREATE TABLE with START TRANSACTION statement.
#
# Bug#35553557 Table creation with foreign key assertion error
#
SET sql_log_bin = OFF;
CREATE TABLE innodb_table1(m INT, FOREIGN KEY (m) REFERENCES t0(f1))
AS SELECT f1 AS m FROM t0;
DELETE FROM t0 WHERE f1 = 1;
ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails (`test`.`innodb_table1`, CONSTRAINT `innodb_table1_ibfk_1` FOREIGN KEY (`m`) REFERENCES `t0` (`f1`))
DROP TABLE innodb_table1;
CREATE TABLE self_referencing_table(pk INT PRIMARY KEY, fk INT,
FOREIGN KEY (fk) REFERENCES self_referencing_table(pk))
AS SELECT 1 AS pk, 1 as fk;
INSERT INTO self_referencing_table VALUES (2,1), (3, NULL), (4,3);
DELETE FROM self_referencing_table WHERE pk=1;
ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails (`test`.`self_referencing_table`, CONSTRAINT `self_referencing_table_ibfk_1` FOREIGN KEY (`fk`) REFERENCES `self_referencing_table` (`pk`))
SELECT pk, fk FROM self_referencing_table ORDER BY pk;
pk fk
1 1
2 1
3 NULL
4 3
DELETE FROM self_referencing_table WHERE pk=3;
ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails (`test`.`self_referencing_table`, CONSTRAINT `self_referencing_table_ibfk_1` FOREIGN KEY (`fk`) REFERENCES `self_referencing_table` (`pk`))
DELETE FROM self_referencing_table WHERE pk=4;
SELECT pk, fk FROM self_referencing_table ORDER BY pk;
pk fk
1 1
2 1
3 NULL
DELETE FROM self_referencing_table WHERE pk=3;
SELECT pk, fk FROM self_referencing_table ORDER BY pk;
pk fk
1 1
2 1
DROP TABLE self_referencing_table;
SET sql_log_bin = @saved_sql_log_bin;
DROP TABLE t0;
|
