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
161
162
163
164
165
166
167
168
169
170
171
|
#
# Bug #19027905 ASSERT RET.SECOND DICT_CREATE_FOREIGN_CONSTRAINTS_LOW
# DICT_CREATE_FOREIGN_CONSTR
#
create table t1 (f1 int primary key) engine=InnoDB;
create table t2 (f1 int primary key,
constraint c1 foreign key (f1) references t1(f1),
constraint c1 foreign key (f1) references t1(f1)) engine=InnoDB;
ERROR HY000: Can't create table `test`.`t2` (errno: 150 "Foreign key constraint is incorrectly formed")
create table t2 (f1 int primary key,
constraint c1 foreign key (f1) references t1(f1)) engine=innodb;
alter table t2 add constraint c1 foreign key (f1) references t1(f1);
ERROR HY000: Can't create table `test`.`#sql-temporary` (errno: 121 "Duplicate key on write or update")
set foreign_key_checks = 0;
alter table t2 add constraint c1 foreign key (f1) references t1(f1);
ERROR HY000: Duplicate FOREIGN KEY constraint name 'test/c1'
drop table t2, t1;
#
# Bug #20031243 CREATE TABLE FAILS TO CHECK IF FOREIGN KEY COLUMN
# NULL/NOT NULL MISMATCH
#
set foreign_key_checks = 1;
show variables like 'foreign_key_checks';
Variable_name Value
foreign_key_checks ON
CREATE TABLE t1
(a INT NOT NULL,
b INT NOT NULL,
INDEX idx(a)) ENGINE=InnoDB;
CREATE TABLE t2
(a INT KEY,
b INT,
INDEX ind(b),
FOREIGN KEY (b) REFERENCES t1(a) ON DELETE CASCADE ON UPDATE CASCADE)
ENGINE=InnoDB;
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
`a` int(11) NOT NULL,
`b` int(11) NOT NULL,
KEY `idx` (`a`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
show create table t2;
Table Create Table
t2 CREATE TABLE `t2` (
`a` int(11) NOT NULL,
`b` int(11) DEFAULT NULL,
PRIMARY KEY (`a`),
KEY `ind` (`b`),
CONSTRAINT `t2_ibfk_1` FOREIGN KEY (`b`) REFERENCES `t1` (`a`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1
INSERT INTO t1 VALUES (1, 80);
INSERT INTO t1 VALUES (2, 81);
INSERT INTO t1 VALUES (3, 82);
INSERT INTO t1 VALUES (4, 83);
INSERT INTO t1 VALUES (5, 84);
INSERT INTO t2 VALUES (51, 1);
INSERT INTO t2 VALUES (52, 2);
INSERT INTO t2 VALUES (53, 3);
INSERT INTO t2 VALUES (54, 4);
INSERT INTO t2 VALUES (55, 5);
SELECT a, b FROM t1 ORDER BY a;
a b
1 80
2 81
3 82
4 83
5 84
SELECT a, b FROM t2 ORDER BY a;
a b
51 1
52 2
53 3
54 4
55 5
INSERT INTO t2 VALUES (56, 6);
ERROR 23000: Cannot add or update a child row: a foreign key constraint fails (`test`.`t2`, CONSTRAINT `t2_ibfk_1` FOREIGN KEY (`b`) REFERENCES `t1` (`a`) ON DELETE CASCADE ON UPDATE CASCADE)
ALTER TABLE t1 CHANGE a id INT;
SELECT id, b FROM t1 ORDER BY id;
id b
1 80
2 81
3 82
4 83
5 84
SELECT a, b FROM t2 ORDER BY a;
a b
51 1
52 2
53 3
54 4
55 5
# Operations on child table
INSERT INTO t2 VALUES (56, 6);
ERROR 23000: Cannot add or update a child row: a foreign key constraint fails (`test`.`t2`, CONSTRAINT `t2_ibfk_1` FOREIGN KEY (`b`) REFERENCES `t1` (`id`) ON DELETE CASCADE ON UPDATE CASCADE)
UPDATE t2 SET b = 99 WHERE a = 51;
ERROR 23000: Cannot add or update a child row: a foreign key constraint fails (`test`.`t2`, CONSTRAINT `t2_ibfk_1` FOREIGN KEY (`b`) REFERENCES `t1` (`id`) ON DELETE CASCADE ON UPDATE CASCADE)
DELETE FROM t2 WHERE a = 53;
SELECT id, b FROM t1 ORDER BY id;
id b
1 80
2 81
3 82
4 83
5 84
SELECT a, b FROM t2 ORDER BY a;
a b
51 1
52 2
54 4
55 5
# Operations on parent table
DELETE FROM t1 WHERE id = 1;
UPDATE t1 SET id = 50 WHERE id = 5;
SELECT id, b FROM t1 ORDER BY id;
id b
2 81
3 82
4 83
50 84
SELECT a, b FROM t2 ORDER BY a;
a b
52 2
54 4
55 50
DROP TABLE t2, t1;
#
# bug#25126722 FOREIGN KEY CONSTRAINT NAME IS NULL AFTER RESTART
# base bug#24818604 [GR]
#
CREATE TABLE t1 (c1 INT PRIMARY KEY) ENGINE=InnoDB;
CREATE TABLE t2 (c1 INT PRIMARY KEY, FOREIGN KEY (c1) REFERENCES t1(c1))
ENGINE=InnoDB;
INSERT INTO t1 VALUES (1);
INSERT INTO t2 VALUES (1);
SELECT unique_constraint_name FROM information_schema.referential_constraints
WHERE table_name = 't2';
unique_constraint_name
PRIMARY
SELECT unique_constraint_name FROM information_schema.referential_constraints
WHERE table_name = 't2';
unique_constraint_name
PRIMARY
SELECT * FROM t1;
c1
1
SELECT unique_constraint_name FROM information_schema.referential_constraints
WHERE table_name = 't2';
unique_constraint_name
PRIMARY
DROP TABLE t2;
DROP TABLE t1;
SET FOREIGN_KEY_CHECKS=0;
CREATE TABLE staff (
staff_id TINYINT UNSIGNED NOT NULL AUTO_INCREMENT,
store_id TINYINT UNSIGNED NOT NULL,
PRIMARY KEY (staff_id),
KEY idx_fk_store_id (store_id),
CONSTRAINT fk_staff_store FOREIGN KEY (store_id) REFERENCES store (store_id) ON DELETE RESTRICT ON UPDATE CASCADE
) ENGINE=InnoDB;
CREATE TABLE store (
store_id TINYINT UNSIGNED NOT NULL AUTO_INCREMENT,
manager_staff_id TINYINT UNSIGNED NOT NULL,
PRIMARY KEY (store_id),
UNIQUE KEY idx_unique_manager (manager_staff_id),
CONSTRAINT fk_store_staff FOREIGN KEY (manager_staff_id) REFERENCES staff (staff_id) ON DELETE RESTRICT ON UPDATE CASCADE
) ENGINE=InnoDB;
SET FOREIGN_KEY_CHECKS=DEFAULT;
LOCK TABLE staff WRITE;
UNLOCK TABLES;
DROP TABLES staff, store;
|
