Debian Codegraph @

mysql-test/suite/innodb/r/rename_fk_2.result

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