Debian Codegraph @

mysql-test/suite/ndb/r/ndb_fk_no_fk_ndb.result

raw
1 SET foreign_key_checks=0;
2 CREATE TABLE t1 (id int primary key, foreign key (id) references t2(id)) ENGINE=engine;
3 CREATE TABLE t2 (id int primary key, foreign key (id) references t1(id)) ENGINE=engine;
4 SET foreign_key_checks=1;
5 SELECT COUNT(*) FROM information_schema.key_column_usage
6 WHERE REFERENCED_TABLE_NAME in ('t1', 't2');
7 COUNT(*)
8 2
9 SET foreign_key_checks=0;
10 DROP TABLE t1, t2;
11 SET foreign_key_checks=0;
12 CREATE TABLE t1 (id int primary key, foreign key (id) references t2(id)) ENGINE=engine;
13 CREATE TABLE t2 (id int primary key, foreign key (id) references t1(id)) ENGINE=engine;
14 SET foreign_key_checks=1;
15 SELECT COUNT(*) FROM information_schema.key_column_usage
16 WHERE REFERENCED_TABLE_NAME in ('t1', 't2');
17 COUNT(*)
18 2
19 SET foreign_key_checks=0;
20 DROP TABLE t2, t1;
21 SET foreign_key_checks=1;
22 SET foreign_key_checks=0;
23 CREATE TABLE t1 (id int primary key, foreign key (id) references no_such_db.t2(id)) ENGINE=engine;
24 drop table t1;
25 SET foreign_key_checks=1;
26 create table t1(pk1 int primary key auto_increment,
27 a int,
28 b int
29 ) engine=engine;
30 create table t2(pk2 int primary key auto_increment,
31 c int not null,
32 unique(c),
33 d int not null,
34 unique(d)
35 ) engine=engine;
36 alter table t1
37 add constraint fk3 foreign key (a) references t2(c),
38 add constraint fk3 foreign key (b) references t2(d);
39 ERROR 42000: Duplicate key name 'fk3'
40 SET foreign_key_checks=0;
41 alter table t1
42 add constraint fk3 foreign key (a) references t2(c),
43 add constraint fk3 foreign key (b) references t2(d);
44 ERROR 42000: Duplicate key name 'fk3'
45 SET foreign_key_checks=1;
46 drop table t1, t2;
47 create table t2 (
48 pk2 int not null primary key,
49 c int
50 ) engine=engine;
51 create table t1 (
52 pk1 int not null primary key,
53 b int,
54 foreign key (b) references t2(pk2)
55 ) engine=engine;
56 set foreign_key_checks=0;
57 truncate table t2;
58 set foreign_key_checks=1;
59 Check if foreign keys are intact after truncate
60 insert into t2 values (1,10),(2,20),(3,30);
61 insert into t1 values (100,1),(200,2),(300,3);
62 insert into t1 values (400,4);
63 ERROR 23000: Cannot add or update a child row: a foreign key constraint fails (`test`.`t1`, CONSTRAINT `t1_fk_1` FOREIGN KEY (`b`) REFERENCES `t2` (`pk2`) ON DELETE NO ACTION ON UPDATE NO ACTION)
64 truncate table t2;
65 ERROR 42000: Cannot truncate a table referenced in a foreign key constraint (<fk_def>)
66 set foreign_key_checks=0;
67 drop table t1, t2;
68 create table t2 (
69 pk2 int not null primary key,
70 c int
71 ) engine=engine;
72 create table t1 (
73 pk1 int not null primary key,
74 b int,
75 foreign key (b) references t2(pk2)
76 ) engine=engine;
77 truncate table t1;
78 set foreign_key_checks=1;
79 truncate table t1;
80 set foreign_key_checks=0;
81 drop table t1, t2;
82 create table t1 (
83 a1 int primary key,
84 b1 int not null,
85 c1 int not null,
86 key (b1,c1)
87 ) engine engine;
88 create table t2 (
89 a2 int primary key auto_increment,
90 b2 int not null,
91 c2 int not null
92 ) engine engine;
93 alter table t2
94 add constraint fk1 foreign key (b2,c2) references t1 (c1,b1);
95 ERROR HY000: Failed to add the foreign key constraint. Missing index for constraint 'fk1' in the referenced table 't1'
96 insert into t1 (a1,b1,c1) values
97 (1,11,12),(2,21,22),(3,31,32);
98 insert into t2 (b2,c2)
99 select c1, b1 from t1;
100 drop table t2,t1;