-- source include/have_ndb.inc create table parent ( a int primary key, b int not null, c int not null, unique(b) using hash, index(c)) engine = ndb; create table child ( a int primary key, b int not null, c int not null, unique(b) using hash, index(c)) engine = ndb; ### # Test that you can't create a FK that violates constraint to start off # # NOTE: these tests use ndb_tools to create/drop FK # as doing it by alter table, would currently mean an offline alter # which doesnt test the same code path # insert into parent values (1,2,3); insert into child values (3,4,5); --let ukname=b\$unique --let oiname=c --error ER_CANNOT_ADD_FOREIGN alter table child algorithm=inplace, add constraint fkname foreign key (a) references parent(a); show warnings; --error ER_CANNOT_ADD_FOREIGN alter table child algorithm=inplace, add constraint fkname foreign key(b) references parent(a); show warnings; --error ER_CANNOT_ADD_FOREIGN alter table child algorithm=inplace, add constraint fkname foreign key(c) references parent(a); show warnings; --error ER_CANNOT_ADD_FOREIGN alter table child algorithm=inplace, add constraint fkname foreign key(a) references parent(b); show warnings; --error ER_CANNOT_ADD_FOREIGN alter table child algorithm=inplace, add constraint fkname foreign key(b) references parent(b); show warnings; --error ER_CANNOT_ADD_FOREIGN alter table child algorithm=inplace, add constraint fkname foreign key(c) references parent(b); show warnings; # # Now it should work... # insert into parent values (2,3,4); insert into parent values (3,4,5); insert into parent values (4,5,6); insert into parent values (5,6,7); alter table child algorithm=inplace, add constraint fk1 foreign key(a) references parent(a); alter table child algorithm=inplace, add constraint fk2 foreign key(b) references parent(a); alter table child algorithm=inplace, add constraint fk3 foreign key(c) references parent(a); alter table child algorithm=inplace, add constraint fk4 foreign key(a) references parent(b); alter table child algorithm=inplace, add constraint fk5 foreign key(b) references parent(b); alter table child algorithm=inplace, add constraint fk6 foreign key(c) references parent(b); alter table child algorithm=inplace, drop foreign key fk1, drop foreign key fk2, drop foreign key fk3, drop foreign key fk4, drop foreign key fk5, drop foreign key fk6; drop table parent, child; # Check multi column foreign keys eval create table parent ( b1 int as (b) stored not null, c1 int as (c) stored not null, a1 int as (a) stored not null, b2 int as (b) stored not null, c2 int as (c) stored not null, a2 int as (a) stored not null, a int default 0, b int default 0, c int default 0, primary key pk1 (a1,b1,c1) using hash, unique key uk2 (a2,b2,c2) using hash ) engine=ndbcluster; eval create table child ( a int default 0, b int default 0, c int default 0, d int default 0, e int default 0, f int default 0, b1 int as (b) stored not null, c1 int as (c) stored not null, a1 int as (a) stored not null, b2 int as (b) stored not null, c2 int as (c) stored not null, a2 int as (a) stored not null, b3 int as (b) stored not null, c3 int as (c) stored not null, a3 int as (a) stored not null, primary key pk1 (a1,b1,c1) using hash, unique key uk2 (a2,b2,c2) using hash, key ok3 (a3,b3,c3), key ok3def (c3,b3,a3,d,e,f) ) engine=ndbcluster; # Add parent row, use different values for the different columns. insert into parent (a, b, c) values (11, 12, 13); # Add valid child row. insert into child (a, b, c) values (11, 12, 13); alter table child add constraint fk11 foreign key (c1,b1,a1) references parent (c1,b1,a1); alter table child add constraint fk21 foreign key (c2,b2,a2) references parent (c1,b1,a1); alter table child add constraint fk31 foreign key (c3,b3,a3) references parent (c1,b1,a1); alter table child add constraint fk12 foreign key (c1,b1,a1) references parent (c2,b2,a2); alter table child add constraint fk22 foreign key (c2,b2,a2) references parent (c2,b2,a2); alter table child add constraint fk32 foreign key (c3,b3,a3) references parent (c2,b2,a2); drop table child, parent;