create table t1 ( a int not null, b int not null, c int not null, d int not null, e int not null, f int not null, primary key (a), unique(c) using hash, index(e)) engine = ndb; alter table t1 add constraint fkname foreign key (a) references t1(a) on delete restrict on update restrict; insert into t1 values (1,1,1,1,1,1); alter table t1 drop foreign key fkname; delete from t1; alter table t1 add constraint fkname foreign key (c) references t1(a) on delete restrict on update restrict; insert into t1 values (1,1,1,1,1,1); insert into t1 values (2,2,3,3,2,2); ERROR 23000: Cannot add or update a child row: a foreign key constraint fails (`test`.`t1`, CONSTRAINT `fkname` FOREIGN KEY (`c`) REFERENCES `t1` (`a`) ON DELETE RESTRICT ON UPDATE RESTRICT) alter table t1 drop foreign key fkname; delete from t1; alter table t1 add constraint fkname foreign key (e) references t1(a) on delete restrict on update restrict; insert into t1 values (1,1,1,1,1,1); insert into t1 values (2,2,2,2,3,3); ERROR 23000: Cannot add or update a child row: a foreign key constraint fails (`test`.`t1`, CONSTRAINT `fkname` FOREIGN KEY (`e`) REFERENCES `t1` (`a`) ON DELETE RESTRICT ON UPDATE RESTRICT) alter table t1 drop foreign key fkname; delete from t1; alter table t1 add constraint fkname foreign key (a) references t1(c) on delete restrict on update restrict; insert into t1 values (1,1,1,1,1,1); insert into t1 values (2,2,3,3,2,2); ERROR 23000: Cannot add or update a child row: a foreign key constraint fails (`test`.`t1`, CONSTRAINT `fkname` FOREIGN KEY (`a`) REFERENCES `t1` (`c`) ON DELETE RESTRICT ON UPDATE RESTRICT) alter table t1 drop foreign key fkname; delete from t1; alter table t1 add constraint fkname foreign key (c) references t1(c) on delete restrict on update restrict; insert into t1 values (1,1,1,1,1,1); alter table t1 drop foreign key fkname; delete from t1; alter table t1 add constraint fkname foreign key (e) references t1(c) on delete restrict on update restrict; insert into t1 values (1,1,1,1,1,1); insert into t1 values (2,2,2,2,3,3); ERROR 23000: Cannot add or update a child row: a foreign key constraint fails (`test`.`t1`, CONSTRAINT `fkname` FOREIGN KEY (`e`) REFERENCES `t1` (`c`) ON DELETE RESTRICT ON UPDATE RESTRICT) alter table t1 drop foreign key fkname; delete from t1; drop table t1; create table t1 ( a int not null, b int not null, c int not null, d int not null, e int not null, f int not null, primary key (a,b), unique(c,d) using hash, index(e,f)) engine = ndb; alter table t1 add constraint fkname foreign key (a,b) references t1(a,b) on delete no action on update no action; insert into t1 values (1,1,1,1,1,1); alter table t1 drop foreign key fkname; delete from t1; alter table t1 add constraint fkname foreign key (c,d) references t1(a,b) on delete no action on update no action; insert into t1 values (1,1,1,1,1,1); insert into t1 values (2,2,3,3,2,2); ERROR 23000: Cannot add or update a child row: a foreign key constraint fails (`test`.`t1`, CONSTRAINT `fkname` FOREIGN KEY (`c`,`d`) REFERENCES `t1` (`a`,`b`) ON DELETE NO ACTION ON UPDATE NO ACTION) alter table t1 drop foreign key fkname; delete from t1; alter table t1 add constraint fkname foreign key (e,f) references t1(a,b) on delete no action on update no action; insert into t1 values (1,1,1,1,1,1); insert into t1 values (2,2,2,2,3,3); ERROR 23000: Cannot add or update a child row: a foreign key constraint fails (`test`.`t1`, CONSTRAINT `fkname` FOREIGN KEY (`e`,`f`) REFERENCES `t1` (`a`,`b`) ON DELETE NO ACTION ON UPDATE NO ACTION) alter table t1 drop foreign key fkname; delete from t1; alter table t1 add constraint fkname foreign key (a,b) references t1(c,d) on delete no action on update no action; insert into t1 values (1,1,1,1,1,1); insert into t1 values (2,2,3,3,2,2); ERROR 23000: Cannot add or update a child row: a foreign key constraint fails (`test`.`t1`, CONSTRAINT `fkname` FOREIGN KEY (`a`,`b`) REFERENCES `t1` (`c`,`d`) ON DELETE NO ACTION ON UPDATE NO ACTION) alter table t1 drop foreign key fkname; delete from t1; alter table t1 add constraint fkname foreign key (c,d) references t1(c,d) on delete no action on update no action; insert into t1 values (1,1,1,1,1,1); alter table t1 drop foreign key fkname; delete from t1; alter table t1 add constraint fkname foreign key (e,f) references t1(c,d) on delete no action on update no action; insert into t1 values (1,1,1,1,1,1); insert into t1 values (2,2,2,2,3,3); ERROR 23000: Cannot add or update a child row: a foreign key constraint fails (`test`.`t1`, CONSTRAINT `fkname` FOREIGN KEY (`e`,`f`) REFERENCES `t1` (`c`,`d`) ON DELETE NO ACTION ON UPDATE NO ACTION) alter table t1 drop foreign key fkname; delete from t1; drop table t1; # # Truncating table with self referencing Foreign key # create table t2 ( pk2 int not null primary key, c int, foreign key (c) references t2(pk2) ) engine=NDB; Foreign keys referenced by test.t2 : t2_fk_1 Foreign keys on test.t2 : t2_fk_1 truncate table t2; Verify all the foreign key are re-created after truncate Foreign keys referenced by test.t2 : t2_fk_1 Foreign keys on test.t2 : t2_fk_1 show create table t2; Table Create Table t2 CREATE TABLE `t2` ( `pk2` int NOT NULL, `c` int DEFAULT NULL, PRIMARY KEY (`pk2`), KEY `c` (`c`), CONSTRAINT `t2_fk_1` FOREIGN KEY (`c`) REFERENCES `t2` (`pk2`) ) ENGINE=ndbcluster DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci drop table t2;