Debian Codegraph @

mysql-test/suite/innodb/t/rename_fk_2.test

raw
1 --echo #
2 --echo # Bug #27453180 FOREIGN KEYS CONSTRAINTS IGNORED AFTER RENAME TABLE
3 --echo #
4
5 SET foreign_key_checks=0;
6
7 CREATE TABLE `country` (
8 `country_id` SMALLINT(5) UNSIGNED NOT NULL AUTO_INCREMENT,
9 `country` VARCHAR(50) NOT NULL,
10 `last_update` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
11 PRIMARY KEY (`country_id`)
12 ) ENGINE=InnoDB;
13
14 CREATE TABLE `city` (
15 `city_id` SMALLINT(5) UNSIGNED NOT NULL AUTO_INCREMENT,
16 `city` VARCHAR(50) NOT NULL,
17 `country_id` SMALLINT(5) UNSIGNED NOT NULL,
18 `last_update` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
19 PRIMARY KEY (`city_id`),
20 KEY `idx_fk_country_id` (`country_id`),
21 CONSTRAINT `fk_city_country` FOREIGN KEY (`country_id`) REFERENCES `country` (`country_id`) ON UPDATE CASCADE
22 ) ENGINE=InnoDB;
23
24 CREATE TABLE `address` (
25 `address_id` SMALLINT(5) UNSIGNED NOT NULL AUTO_INCREMENT,
26 `address` VARCHAR(50) NOT NULL,
27 `city_id` SMALLINT(5) UNSIGNED NOT NULL,
28 `postal_code` VARCHAR(10) DEFAULT NULL,
29 `last_update` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
30 PRIMARY KEY (`address_id`),
31 KEY `idx_fk_city_id` (`city_id`),
32 CONSTRAINT `fk_address_city` FOREIGN KEY (`city_id`) REFERENCES `city` (`city_id`) ON UPDATE CASCADE
33 ) ENGINE=InnoDB;
34
35 INSERT INTO country (`country_id`, `country`) VALUES
36 (1, 'Canada'),
37 (2, 'USA'),
38 (3, 'Mexico'),
39 (4, 'France'),
40 (5, 'Spain');
41
42 INSERT INTO city (`city_id`, `city`, `country_id`) VALUES
43 (1, 'Montral', 1),
44 (2, 'New York', 2),
45 (3, 'Durango', 3),
46 (4, 'Paris', 4),
47 (5, 'Madrid', 5);
48
49 INSERT INTO address (`address_id`, `address`, `city_id`, `postal_code`) VALUES
50 (1, 'addy 1', 1, '10000'),
51 (2, 'addy 2', 2, '20000'),
52 (3, 'addy 3', 3, '30000'),
53 (4, 'addy 4', 4, '40000'),
54 (5, 'addy 5', 5, '50000');
55
56 SET foreign_key_checks=1;
57
58 --error ER_ROW_IS_REFERENCED_2
59 DELETE FROM country WHERE country_id =1;
60
61 --sorted_result
62 SELECT * FROM INFORMATION_SCHEMA.INNODB_FOREIGN;
63 --sorted_result
64 SELECT * FROM INFORMATION_SCHEMA.INNODB_FOREIGN_COLS;
65 CREATE TABLE tmp_country LIKE country;
66 INSERT INTO tmp_country SELECT * FROM country;
67
68 SET foreign_key_checks=0;
69
70 DROP TABLE country;
71
72 RENAME TABLE tmp_country TO country;
73
74 SET foreign_key_checks=1;
75
76 --error ER_ROW_IS_REFERENCED_2
77 DELETE FROM country WHERE country_id =1;
78
79 --sorted_result
80 SELECT * FROM INFORMATION_SCHEMA.INNODB_FOREIGN;
81 --sorted_result
82 SELECT * FROM INFORMATION_SCHEMA.INNODB_FOREIGN_COLS;
83
84 DROP TABLE address;
85 DROP TABLE city;
86 DROP TABLE country;
87
88 --echo #
89 --echo # Bug #29686796 RENAMING A MEM TABLE WITH FK WHILE FOREIGN_KEY_CHECKS
90 --echo # IS SET 0 RESULTS IN CRASH
91 --echo #
92
93 CREATE TABLE t1(f1 INT,f2 INT)ENGINE=MEMORY;
94 set foreign_key_checks=0;
95 CREATE TABLE t2
96 (id INT KEY,
97 t1_id INT,
98 INDEX par_ind (t1_id,id),
99 FOREIGN KEY(t1_id) REFERENCES t1(id) ON DELETE CASCADE) ENGINE=INNODB;
100 RENAME TABLE t1 to t5,t2 to t1;
101 DROP TABLE t1, t5;
102 SET foreign_key_checks=1;