| 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; |